Viewing 15 posts - 13,546 through 13,560 (of 22,224 total)
Getting the exact same plan with that much data between a table variable & a temp table indicates something else is going on. Is auto-create stats or auto-update of stats...
"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
January 20, 2011 at 11:14 am
Steve Jones - SSC Editor (1/20/2011)
"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
January 20, 2011 at 10:52 am
We had a similar situation. A procedure (with a 76 table join) that was called hundreds of times in an hour. It hit so many tables and the data was...
"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
January 20, 2011 at 10:41 am
Table variables like you're using show as having a single row because they don't have statistics. This can lead to seriously problematic execution plans, like you're seeing. If you have...
"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
January 20, 2011 at 10:34 am
Personally, I wouldn't run it as a script. I'd use SQL Agent and a SQL Agent job with steps. It'll be much more functional, better error trapping, etc. If not...
"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
January 20, 2011 at 8:28 am
It's going to be difficult to put 2tb into 100gb, virtual or not.
"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
January 20, 2011 at 8:23 am
I actually just, just found another way you can do it. You can use extended events to capture all wait states on procedures as they occur. I don't have the...
"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
January 20, 2011 at 8:22 am
That could work, but what other processes will be on the server? How much data do you have? I'm asking because i assume you're going virtual because you're going to...
"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
January 20, 2011 at 8:14 am
sql_butterfly (1/20/2011)
Understood. It means as per thumb rule, the funtionality of a sproc should be as specific as possible and we should try to normalize the sproc as well.
Just so...
"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
January 20, 2011 at 8:05 am
GilaMonster (1/20/2011)
Grant Fritchey (1/20/2011)
My most recent post was about why do log files fill up. Believe me, other people have posted about it.
A topic I'm in the middle of writing...
"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
January 20, 2011 at 7:38 am
This is a somewhat big topic. There are probably 15-20 measures, minimum, that you ought to capture and interpret. You should also start capturing wait states to see what things...
"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
January 20, 2011 at 7:32 am
You basically can do two things, sample the waiting processes regularly during the day to see if you can catch a query waiting because of cxpacket or set up a...
"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
January 20, 2011 at 7:29 am
Multi-statement user defined functions & table variables are estimated based on 1 row, so their "cost" estimates are extremely low when compared to regular & temp tables that have actual...
"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
January 20, 2011 at 7:27 am
Really hard to be sure without seeing the execution plan.
"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
January 20, 2011 at 7:21 am
It's pretty much whatever comes in front of you from day to day that you are either fascinated with, excited by or knowledgeable about. Preferably all three. You can't worry...
"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
January 20, 2011 at 7:15 am
Viewing 15 posts - 13,546 through 13,560 (of 22,224 total)