question

AnilMenariya avatar image
AnilMenariya asked

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 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

6 Answers

· Write an Answer
erlokeshsharma08 avatar image
erlokeshsharma08 answered

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 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

AnilMenariya avatar image
AnilMenariya answered

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

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

erlokeshsharma08 avatar image
erlokeshsharma08 answered

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
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

JohnM avatar image
JohnM answered

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 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

AnilMenariya avatar image
AnilMenariya answered

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 comment
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

erlokeshsharma08 avatar image erlokeshsharma08 ·

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

0 Likes 0 ·
AnilMenariya avatar image
AnilMenariya answered

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

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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