April 28, 2011 at 8:53 pm
Comments posted to this topic are about the item Killing Statistics
April 28, 2011 at 8:53 pm
Good question and something worth learning
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
April 28, 2011 at 11:41 pm
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2011 at 12:01 am
Learn new thing, thanks
April 29, 2011 at 4:10 am
Thanks for the question Steve, I learnt something today 🙂
M&M
April 29, 2011 at 5:28 am
Glad to learn that but why would you have to run that?
Even if by some accident you unintentionnally started update stats with fullscan on a 2 TB table, you could still kill your spid to stop it.
I'm curious, has anybody ever needed to do that?
April 29, 2011 at 6:13 am
Good question.
One pedantic niggle about the explanation: KILL STATS JOB doesn't take a job id from the system view, it takes a job id you give it as a parameter, so you have to get it from the system view yourself.
Tom
April 29, 2011 at 6:16 am
Ninja's_RGR'us (4/29/2011)
Glad to learn that but why would you have to run that?Even if by some accident you unintentionnally started update stats with fullscan on a 2 TB table, you could still kill your spid to stop it.
But if it's an asynchronous job it's not running in your spid - how to you find the spid for it?
I'm curious, has anybody ever needed to do that?
Not me, for sure.
Tom
April 29, 2011 at 6:30 am
Tom.Thomson (4/29/2011)
Ninja's_RGR'us (4/29/2011)
Glad to learn that but why would you have to run that?Even if by some accident you unintentionnally started update stats with fullscan on a 2 TB table, you could still kill your spid to stop it.
But if it's an asynchronous job it's not running in your spid - how to you find the spid for it?
I'm curious, has anybody ever needed to do that?
Not me, for sure.
What's hard to wrap my mind around is the need to stop a process like that because the server is under too much pressure. I'm sure it's possible but then again even if our biggest tables got all their stats updated at the same time. I would never have enough time to get the call and open ssms. Granted our biggest tables only have a few GBs and a fullscan stats update is done is seconds.
That's why I'm asking who ever had to run that and under what circumstances?
April 29, 2011 at 6:41 am
Ninja's_RGR'us (4/29/2011)
Tom.Thomson (4/29/2011)
Ninja's_RGR'us (4/29/2011)
Glad to learn that but why would you have to run that?Even if by some accident you unintentionnally started update stats with fullscan on a 2 TB table, you could still kill your spid to stop it.
But if it's an asynchronous job it's not running in your spid - how to you find the spid for it?
I'm curious, has anybody ever needed to do that?
Not me, for sure.
What's hard to wrap my mind around is the need to stop a process like that because the server is under too much pressure. I'm sure it's possible but then again even if our biggest tables got all their stats updated at the same time. I would never have enough time to get the call and open ssms. Granted our biggest tables only have a few GBs and a fullscan stats update is done is seconds.
That's why I'm asking who ever had to run that and under what circumstances?
Oh, I agree with you totally about that. That's what I meant to convey by that "for sure", I guess I got that bit of English usage wrong.
Tom
April 29, 2011 at 6:44 am
I understood what you meant, I just felt I had to clarify even further... nothing wrong wiht ioure anglich dud! 😉
April 29, 2011 at 7:09 am
Ninja's_RGR'us (4/29/2011)
Even if by some accident you unintentionnally started update stats with fullscan on a 2 TB table, you could still kill your spid to stop it.
It's asynchronous so I would assume killing the SPID wouldn't affect the job. Especially because, at least theoretically, the query can be done before the asynchronous stats up date is done.
I looked around online and the only thing I saw about using it is to make sure that when you are going to single user mode to make sure that the async job doesn't grab the only session.
April 29, 2011 at 7:18 am
cfradenburg (4/29/2011)
Ninja's_RGR'us (4/29/2011)
Even if by some accident you unintentionnally started update stats with fullscan on a 2 TB table, you could still kill your spid to stop it.It's asynchronous so I would assume killing the SPID wouldn't affect the job. Especially because, at least theoretically, the query can be done before the asynchronous stats up date is done.
I looked around online and the only thing I saw about using it is to make sure that when you are going to single user mode to make sure that the async job doesn't grab the only session.
Ya that would be a PITA.
Maybe I missed it but I can't see a with async option on the create/update stats command. Does that mean that the setting is only at the db level and that if you have async ON and then you issue an update stats command, then it does like sp_startjob and does't wait for completion before completing the batch?
April 29, 2011 at 7:37 am
Lucky guess for me and a great question! Another interesting tidbit.
A word of advice for everyone who is interested in these little morsels of SQL filler.
Read the MCM reading list. It is full of stuff like this.
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 29, 2011 at 8:18 am
Thanks Steve,
Learned something... not sure that I'll need to use it any time soon, but it's good to know.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply