SQL 2008R2 TEMPDB
We have a very critical report that runs a stored procedure on SQL 2008 R2. When the sp is executed it starts creating tempdb internal objects. I normally run a query to check these outstanding internal objects page counts and I can see the count of these objects increasing. Initially the tempdb has 100% free space in the datafiles but as this stored proc runs it fills up the 300 GB tempdb drive. The query will continue running for hours until it times out due to disk space issue and lack of space in the tempdb database. I restored the database in the test instance and ran the same report and it executes within 3-5 minutes and it doesn't fill up the tempdb. The difference between test and prod is that test is SQL 2014 and prod is SQL 2008. Also, I am updating the stats with FULL scan every second day and index fragmentation is well under control. Can any Gurus please point me in the right direction to troubleshoot this issue?
AND @enddate BETWEEN orange.start_dttm AND ISNULL(orange.enddate,DATEADD(dd, 1, @enddate))
Hi John, Thank you for replying.
We identified that in the where clause of the query it was comparing million of records of date type to a null value which was really not required by the business rule . The comparison was taking place in the tempdb. We commented it out in the SP and the report is now running in 28 secs.
Great find. what was the exact condition please. Something like datecolumn <> NULL
Look at the execution plans between the two servers. The query optimizer changed in 2014. If you can post the execution plans here, we might be able to help fix the issue.
Can you run following queries in both test and prod and provide results please
SELECT name AS FileName, size*1.0/128 AS FileSizeinMB, CASE max_size WHEN 0 THEN 'Autogrowth is off.' WHEN -1 THEN 'Autogrowth is on.' ELSE 'Log file will grow to a maximum size of 2 TB.' END AutogrowthStatus, growth AS 'GrowthValue', 'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.' WHEN growth > 0 AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.' ELSE 'Growth value is a percentage.' END FROM tempdb.sys.database_files; GO
Yes, the database has been restored from the latest full and tlog backups and I am running the sp from SSMS
Comment - is the volume of data same in both test and prod? Are you able to run the sp through SSMS in PROD (assuming that you run into the issue while report is being generated)?
