February 1, 2012 at 3:40 pm
I have a junior developer running a pretty long and complex query that uses a lot of left joins, and a lot of aggrations and many subqueries. She said she ran the query and got an error message
" Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup"
It runs and make the tempdb database run out of space, I tried to increase the size to a pretty large size, still the query hangs, and excuting forever. There are no temp tables, nor temp sproc, nor table variables in the query, no sort, but somehow it still uses tempdb, but I guess it may use it a temp storage and spooling area, correct?
But even I enlarge the size to a pretty big size, it is still running and hanging there and make the tempdb size to a negative number.
If you are interested, the query is in the attachment
What would you recommend in this case?
Thanks
February 1, 2012 at 4:13 pm
Any good reason a where clause is not being used?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 1, 2012 at 4:17 pm
Not sure about the detail of the query. But they are research and evaluation department, I guess they used it to combine diffrent years data for some kind of reports.
February 1, 2012 at 4:21 pm
The query is filling up the tempdb probably due to the size of the tables in the query. Reduce the result set and likelihood of filling up the tempdb by using a where clause.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 1, 2012 at 4:22 pm
It also looks like you will be seeing a significant sort operation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 1, 2012 at 4:25 pm
SQLRNNR (2/1/2012)
It also looks like you will be seeing a significant sort operation.
When you say above, do you mean the last statement in the query: ORDER BY ss.School
Or where else you can see they will use a lot of sort.
I did help her to remove the last statement order by school, but the query still runs like that.
Thanks
February 1, 2012 at 4:28 pm
That last order by statement. But I wouldn't be surprised to see other sort operations if the query were to complete and an execution plan could be generated. But you need to be able to get it to complete first, which means a where clause will be necessary.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 2, 2012 at 7:28 am
Take a look at the execution plan for the query. see what it's doing. It could be you're building lots of temporary storage processes (hash tables, sorts, work tables, etc.). Understanding where the issues are occurring is the first step towards fixing them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2012 at 9:21 am
Firstly when you say you set the TEMPDB size to very large, can you tell us the exact size in GBs?
Run the following two queries and determine what are the contents of TEMPDB and what SPID is consuming the space.
DECLARE @runtime datetime
SET @runtime = GETDATE()
PRINT '-- sys.dm_db_file_space_used'
select CONVERT (varchar(30), @runtime, 121) AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
RAISERROR ('', 0, 1) WITH NOWAIT
PRINT '-- sys.dm_db_session_file_usage'
select top 10 CONVERT (varchar(30), @runtime, 121) AS runtime, * FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC
RAISERROR ('', 0, 1) WITH NOWAIT
Read the following article for explanation of all the objects in TEMPDB and determine what uses the maximum space in TEMPDB. Next check the SPID that is using most of the space. I believe you have already know that but I would still check this query to find the SPID and then the query.
http://technet.microsoft.com/en-us/library/cc966545.aspx
Blog
http://saveadba.blogspot.com/
February 2, 2012 at 10:35 am
Another thing you could do that would help with the size is to remove
S.SchoolName, S.School
from each one of the subqueries. It is not needed or referenced outside of the subquery. Depending on the rowcount of each subquery that may actually save a bit of tempdb space by not returning data you don't need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply