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?
Answer by AnilMenariya ·
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.
Answer by erlokeshsharma08 ·
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