SQL 2008R2 TEMPDB

Question

question

AnilMenariya asked May 15, '18

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?

tempdb

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-05-15T07:29:57Z

erlokeshsharma08 answered May 15, '18

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)?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2018-05-15T08:37:33Z

AnilMenariya answered May 15, '18

Yes, the database has been restored from the latest full and tlog backups and I am running the sp from SSMS

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2018-05-15T08:56:46Z

erlokeshsharma08 answered May 15, '18

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 &gt; 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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2018-05-15T12:09:38Z

JohnM answered May 15, '18

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2018-05-16T06:30:42Z

AnilMenariya answered May 16, '18

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.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 commented · May 16, 2018 at 09:25 AM

Great find. what was the exact condition please. Something like datecolumn <> NULL

0 ·

Answer 6 · 2018-05-16T22:26:12Z

AnilMenariya answered May 16, '18

AND @enddate BETWEEN orange.start_dttm AND ISNULL(orange.enddate,DATEADD(dd, 1, @enddate))

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question