April 2, 2017 at 11:39 pm
Hi there,
I need your help with regards to a query that is running on SQL Server 2012 64bit Developer edition (UAT) and Windows 2012 Ent 64bit
The same query runs on PROD on Windows 2008 R2 64bit and SQL Server 2012 64bit Enterprise Edition, the issue i'm having is that the same query on PROD runs without using up the tempdb space but in UAT it uses up the tempdb space filling up the drive space then the query crashes with the below error
Tempdb config is 4 files set @ 200mb each with 20%growth as per the number of processors available to the box, also have enabled 1117 and 1118
"Executing the query "exec procPopEmployeeOracleHRMS" failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run storage: 140740507271168' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
What more can I look at?
Thanks
It's better to fail while trying, rather than fail without trying!!!
April 3, 2017 at 12:56 am
Hi,
did you for real use just 200 MB at 4 tempdb files?
I think, this is not much place to work for a sql server?
Kind regards,
Andreas
April 3, 2017 at 1:46 am
Hi,
Isn't based on the number of processors on the box to create the same number of tempdb data files? I guess will have to up the disk size will advise
Thanks
It's better to fail while trying, rather than fail without trying!!!
April 3, 2017 at 2:52 am
You allocated 800mb for tempdb, but you left the growth on. How much space is on the drive? It's going to attempt to grow to fill the space needed to perform your sort operation if it's available. When allocating this, think about the size of your data, your queries, and the work they're doing. Lots and lots of stuff goes into tempdb. It needs to be on a drive large enough to accommodate what you're doing in your T-SQL. Sorting data, hash joins, aggregates, all these (and tons more things) use temporary storage to accomplish what they're doing. Let's say you have a query that pulls 1,000 rows from one table and 1,000 rows from another. The tables aren't indexed well, so a hash join is used to put the data together. This means a temporary table is created into which is stored some of the data for checking against the other data. Depending on the size of your keys and probes, how quickly do you think 800mb could be used up, for just this one query. Now, add in a bunch of other queries. Now how much tempdb space do you need? It's absolutely not a question of just setting X number of files. It's a question of having adequate space.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2017 at 8:17 am
smthembu - Sunday, April 2, 2017 11:39 PMHi there,I need your help with regards to a query that is running on SQL Server 2012 64bit Developer edition (UAT) and Windows 2012 Ent 64bit
The same query runs on PROD on Windows 2008 R2 64bit and SQL Server 2012 64bit Enterprise Edition, the issue i'm having is that the same query on PROD runs without using up the tempdb space but in UAT it uses up the tempdb space filling up the drive space then the query crashes with the below error
Tempdb config is 4 files set @ 200mb each with 20%growth as per the number of processors available to the box, also have enabled 1117 and 1118
"Executing the query "exec procPopEmployeeOracleHRMS" failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run storage: 140740507271168' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
What more can I look at?
Thanks
Just a thought... I've frequently found such things as this to occur because few people spend any time at all on the maintenance of statistics on non production environments. That can cause queries to come up with bad plans and the bad plans frequently involve a whole lot of TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2017 at 12:32 am
Thanks for the advises
The maintenance is done well on my side. The puzzling thing is after the OS upgrade on non-prod env i'm having this issue of tempdb shooting through the roof, but on PROD that's still on 2008 R2 OS Ent 64bit 200gb drive is not filling up and the ETL completes fine everyday
Not sure if the OS has something to do or ??????
It's better to fail while trying, rather than fail without trying!!!
April 4, 2017 at 2:55 am
Differences in the server settings? Differences on database settings? Differences on query plans being generated? It's so hard to say for certain without a lot more knowledge that it's absolutely Setting X that is causing the problem. I wouldn't assume that a change to the OS alone would change the behavior of tempdb. Did the memory allocation change... something. I mean, the problem we're facing is that you're comparing two different versions of SQL Server on top of a changing OS, etc. So much stuff is different that there's not a way to identify a single possible suggestion. Everything we've put forward is pretty base speculation.
Start monitoring the server to see what the wait statistics are. Capture query metrics and query plans to understand what is running slow and why. Traditional monitoring and query tuning. However, getting everything on the same versions of OS & SQL Server will sure make this easier. You're comparing different things to different things and finding differences. Yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2017 at 12:37 pm
are both same exactly the same spec?
mainly memory but also
number cores
size of databases - are they same size as prod? - having one or more tables with significant differences from prod can cause tempdb plans
table statistics - are they same as prod?
max dop
cost threshold for parallelism
resource governor - I've seen it enable on DEV/UAT for trials and also to prevent bad testing queries from hogging server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply