SQL Server 6.5
I am running a Query in my Production Server. It is hardly taking 15 Mins. The same Query is taking more than 3 Hours in my test server. I can see the only difference between these two servers is Tempdb Size. Will tempdb size matters the performance of a Query. Can anyone reply me?
Thanks in Advance
Krishnakumar
Hi,
What should be the size of TEMPDB should i set ,if my production databse is 500 mb to 1 gb.
Thanks
Nil.
I have an SQL server that has probably 20 or so people connected at a time. In addition to these people, there are five people that run Crystal reports with some voluminous data, usually coming from views.
About every week and a half, one of the report writers get a message that the master device is full and must be expanded, because tempdb is full. When I expand temdb, it fixes the problem.
My question is, the tempdb is now 800 Megs. The sum total of memory allocated to all of the other(besides master)devices is 650. Should this be a flag that something is wrong? It just keeps growing and growing. Tempdb is not on it's own device-it is still on master.
Any advice would be greatly appreciated!
Jane Davis
when I restart SQL server my tempdb database is 1.9Gb. I thought it was deleted and replaced with a copy of the model database. Is this not so?
View 2 Replies View RelatedI have read that MS suggests you tempdb be atleast 25% of your largest database. Is there any way to monitor your tempdb to determine if it is filling up or being 100% utilized and needs to be expanded?
Thanks,
David
I have a server with 2.5GB of RAM. I am allocation 1GB to SQL Server and am attempting to put 500MB tempdb in RAM. It would appear as though I am hitting some sort of max threshhold that I am unaware of as I can set tempdb in RAM up to 320MB. Anything beyond that and I get the following errors on start up and SQL Server will not start.
98/12/02 08:25:47.03 spid1 Clearing temp db
98/12/02 08:25:47.03 kernel udactivate(IN_RAM): Operating system error 8(Not enough storage is available to process this command.) encountered
98/12/02 08:25:47.03 spid1 Device activation error. The physical filename 'IN_RAM' may be incorrect
98/12/02 08:25:47.03 spid1 crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't exist, cannot be created, or doesn't have enough space for tempdb
I am certain that I am not hitting a Physical RAM limit and the memory checks out and is visible to NT. Please Advise. Thanks
What should be size of tempdb.
I am daily getting tempdb translog is full error.
Then i need to dump the transaction with dump traan.... with no log.
After this only server working fine.
Any solution please...
Thanks in Advance.
Selvam
Hello Everyone!
Is there any way by which I can determine whether the tempdb size for the SQL Server is enough or not? (I ean are there any symptoms like excessive paging etc. that can identify this bottleneck?).
Thanks!
We had a runaway query which built the size of tempdb to 24000mb. Then someone changed the unrestricted file growth property to restricted growth while the size was 24000mb. Now I can not reduce the initial size. I have set the property back to unrestricted file growth. I have shrunk the tempdb and available space is almost 24000mb. I have stopped sqlserver. I even deleted the existing tempdb.mdf & tempdb.ldf files. But when SQL server is restarted, the initial size is set to 24000mb. It will not let me reduce the size. Is there anything short of manipulating the system tables to reduce the size back to 500mb?
View 5 Replies View RelatedWe currently have a hard-drive size of 3.89MB and 3.3MB is being used by tempdb. I have tried shrinking the database truncateonly but this is not working. The problem is that the tempdb file is as large as my C: drive size. In addition can this be moved to another directory. For example can I move the tempdb.mdf and ldf from C: to E:. Any help would be greatly appreciated.
Thanks
Hi All,
On my server C drive is of 34GB. Right now tempdb size is 22GB which is causing C drive to be full. How I can I reduce it? I dont want to move tempdb to any other drive, and I am only looking a way to reduce its size.
Please help quickly....
Thanks,
Zee
I have the classic "tempdb-out-of-space" problem. Unfortunately, my server fails to reboot properly as tempdb is located on the C: drive which is now completely full. While I understand the changes required to prevent this from happening again, I want to know if it will even reboot if I delete tempdb.mdf and tempdb.ldf. I've read conflicting information on MSDN about default tempdb file size:
- files are built to the default size (I will be fine)
- files are built to the same size as before (problem)
Which is true for SQL 2005?
Has anyone seen the SQL Server error:
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike
I have a tempdb that was created at 1Gig. I don't know why but I want to shrink it below the original creation size. Is there a way to shrink this file or create a new file and delete the old?
I have tried shinkfile and shrink database with no luck.
Thanks Jeff
Against my better judgement, we are using fixed allocations of tempdb on some of our servers. This is to deal with specific limitations of our applicaitons and hardware configuration that I'm not allowed to discuss in much detail.
The problem that I have is that the present plan is to configure the data file at around 18 Gb and the log file at around 2 Gb. This seems just plain wrong to me, but I haven't been able to find a formal recommendation that gives any relative sizing. I would expect to have about twice as much log as data space, especially for tempdb.
Does anyone know of a formal citation (preferably from Microsoft) that discusses this?
-PatP
We have a problem with the size of the tempdb.mdf file. The tempdb had grown to 25Gb and consumed all the available disk space. SQL server was restarted and the tempdb was reset back to the default size. The following day the tempdb suddenly increased in size from 200mb to 25GB within a very short space of time. There were a couple of event log entries from sqlservger regarding the lack of disk. Since then the server is running without any problems but the level of free space is virtually zero on the drive with tempdb.mdf file.
What would cause the tempdb to grow suddenly and to this size?
Can I control the size the tempdb can grow to?
SQL 2005 (x64) sp1
W2K3 R2 SP1
I am considering turning on RCSI to prevent blocking, how do I estimate the size of tempdb?
I am monitoring current tempdb usage of space for
UserObjectsUsageKB,
InternalObjectsKB,
VersionStoreKB,
FreeSpaceKB. Â
I have got SQLv6.5 SP5a with SMS1.2 SP4 on seperate Alpha boxes. I have automated the backups so they are scheduled for after hours. SMS gets backed up first and TEMPDB shortly afterwards. However, since a back log in SMS MIFS has happened, the TEMPDB backup displays of 100,000pages backed up. When you back it up on its own, it only shows 170+ pages.
The SMS DB is 600MB in size, the Log is 210MB, Open objects is 5000, and TEMPDB is set 210MB on its own device.
Any ideas
My prod server (only default instance) is configured TempDB 1024 MB data and Log 200MB. when I run 'sqlperf logspace' it shows most of time around 45% 'log space used'. There nothing going on the instance when I ran 'whoisactive' and select * from sys.sysprocesses where dbid = 2!!!
So my questions are is this normal to see log space around 45%, how to find what what CAUSED the tempdb log space to grow 45%? Is there something to do about it?
Hi all,
I've been trying to rack my brains over this one; When I use this select statement in SqlDataAdapter, it runs fine; it takes about 4 to 5 seconds to run. In SqlDataSource, its very erratic; taking between 8 to 71 seconds to run. I've finally narrowed down the issue to something that resolves the symptoms to something in my query;
SELECT f.UnitID, f.FacilityName, SUM(CASE WHEN t .sData_Type = 'CLINICAL' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Clinical, SUM(CASE WHEN t .sData_Type = 'OTHERS' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Others, SUM(t.nWrite_Off_Amt) AS Sum_Total, SUM(t.nCashAmount) AS Sum_Cash, t.iYearFROM t LEFT OUTER JOIN d ON t.sUnitID = d.sUnitID AND t.sPat_Num = d.sPat_Num AND t.iMonth = d.iMonth AND t.iYear = d.iYear LEFT OUTER JOIN tblFac AS f ON t.sUnitID = f.UnitID_1WHERE (t.iYear = @year) AND (t.iMonth = @month) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))GROUP BY f.UnitID, t.iYear, f.FacilityName, f.Division
If I change my WHERE clause to the following
WHERE (@year = t.iYear) AND (@month = t.iMonth) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))
performance is MUCH better. How could such a little change make such a big difference in speed?
Thanks.
-e
My Sql 2005 SP1 has performance trouble this afternoon. I
noticed a lot PageLatch_up and PageLatch_sh waiting with 2:1:1. I think the
problem may be caused by the tempdb. There are more than 1000 temp tables. The number
of user connections is abnormally high. “SELECT *
FROM tempdb.dbo.sysobjects� cannot be finished due to blocking.
I restarted the server to fix
the problem temporaryly.
Any clue to fix the problem? Does it help to replace temp
table with table variable in stored procedures? “Drop temp table� are called in
procedures, why the temp tables are still in the tempdb?
Thanks for your help.
We think we're having performance problems, and among the areas of investigations is the tempdb database. Since it resets itself after SQL is restarted, is there a way to find out how big it has grown in the past ? Does leaving it at the default size cause a performace hit ?? Right now it's 8.75 MB, with 7.38 MB available, which sounds pretty harmless.
Any thoughts ?
Hi,
We are thinking about buying new harddrives to improve sql server performance. Currently TEMPDB is running on a dedicated RAID 0 with 3 harddrives of 136 GB, 10.000 RPM. When running a large bulk insert within a SSIS package to 15 destination tables we notice high numbers in the Avg. and current Read Queue length (above 3000) of the drive where TEMPB is on. No other programs or swap file is using this RAID 0 drive. Can anyone tell me if it is worth buying 4 harddrives of 15.000 RPM each 33 GB big replacing the current 3 drives? How much impact will it have on the Avg. and Current Read queue length and will it improve the time sql server needs to bulk insert data?
Thanks.
Marc
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , My perform counter gives me data as such
Free Space in tempdb (KB)Â Â Â Â Â Â Â Â Â Â Â Â Â Â 279938496
Version Generation rate (KB/s)Â Â Â Â Â Â Â Â Â Â 53681040
Version Cleanup rate (KB/s)Â Â Â Â Â Â 53422320
Version Store Size (KB)Â Â Â Â Â 258720
Version Store unit count      22
Version Store unit creation                     774
Version Store unit truncation        752
We have an application that runs across the WAN to multiple locations. Performance is poor and we are looking at ways to improve performance. One suggestion from our Sr. Network Administrator is to change our Network Packet Sizes across all points, SQL Server & NIC to match the outgoing Router. This would be a size of 1440.
Does anyone have any thoughts or recommendations on this?
Hello, everyone:
I am not sure wether transaction log file size affect the database performance. My SQL 2K suddenly became slow yestoday. The data file is 3GB, and transaction log file is 11GB. Someone suggested I should shrink transaction log file. Can it work?
Thanks a lot.
ZYT
Hello,
Working with SQL Server 2000, I have a table with the following structure:
ID (INT)
userID (INT, foriegn key)
productID (INT)
productQTY (DECIMAL(5,2))
purchaseDate(smalldatetime)
I have about a 1000 users, entering about 20-30 rows per day each, i.e ~20,000 - 30,000 new rows per day. The table might be queried with a simple "SELECT" for the products a user ordered per day or per time frame (purchaseDate column).
My question (finally) is - when should I expect to see performance degradation? Is there anything I can do to prevent it (i.e splitting this table somehow to several tables)?
Thank you all in advance
I just upgraded our application from SSCE 2.0 to SQL Mobile. Our app is written in C++, and we use OLE DB for most of our queries, including the routine the downloads and inserts our lookup table data. This application is running on a Dell Axim X51.
Using SSCE 2.0, this routine takes 236 seconds, with most time spent inserting data into various tables (using OLE DB). The resultant database size is 15.1 MB.
Using SQL Mobile, this routine now takes 675 seconds, with a resultant database size of 27.9 MB!! There is a noticable increase in time when the downloaded data is being inserted into the database.
What would be the reason(s) for the slower performance and the increased size of the database? This appears to be a monumental step backwards in performance. Any suggestions regarding improving the perfomance and size?
Hello!
I have a general question concerning the impact on the performance of massive parallel data imports in one SSIS-package.
We have a Database on a SQL2005 SP1-Server (2 Xeons 3,8 Ghz, 4GB of RAM) for a report web-app which is updated every day with data of the last year/3 years. The data is extracted from several different DBs on multiple machines at different locations. Right now, there are imports/transformations from 7 companies at 3 locations. The table has ~80 columns and about 2 Mio. Rows. I built a SSIS-Package with one companies import and added the others by c&p-ing all the tasks in the package and changing connection parameters and values. Soon there will be 6 more companies to do imports with, and there will possibly be about 20 some day.
Now, when these 7 imports run parallel, there are 3 simultaneous imports from the same Source Server. Sometimes one of these imports seem to hang up. I cannot reproduce it, when I run the package 2 or 3 times, it´s gone. So I put some of the imports in line to reduce the parallel working tasks to 4. Then the problem dissappears. The "MaxConcurrentExecutables" Value is set to 6. "Retain same connection" is set "TRUE".
My questions, regarding stability and performance, are:
1.) Is it better to do those imports in seperate packages, if yes can I schedule multiple packages to execute parallel at the "SQL Server Agent"?
2.) Or should they be combined in one package, running (partly) parallel?
3.) What is the appropriate value for the "MaxConcurrentExecutables" Value and what options do I have to speed up those imports?
Hello All,
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Thanks for your help!
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View Related