July 1, 2011 at 4:58 am
Hi,
One of my developers has run a select script but he didn't save the codes. Is there a way I can pull this out from proc cache considering we use Simple mode?
Thanks
July 1, 2011 at 6:08 am
If Code is executed then execute this script
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS ='SLEEPING'
ORDER BY CPU DESC
if code is executing then execute this script
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 1, 2011 at 6:15 am
Maybe, depends whether the query has flushed out of cache.
SELECT text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
You'll have to do some filtering.
Btw, this has nothing to do with the transaction log, selects aren't logged in any way (they aren't database modifications). The caching of query plans is independent of recovery model, implemented in a different portion of the engine for completely different reasons
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2011 at 6:16 am
Syed Jahanzaib Bin hassan (7/1/2011)
If Code is executed then execute this scriptSELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS ='SLEEPING'
ORDER BY CPU DESC
That requires that the connection is still open. If the developer closed the query window, then his connection is closed and won't appear in the list of connected sessions.
btw, why do you persist in writing queries against a compatibility view that is deprecated and scheduled for removal. You're teaching people bad practices.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2011 at 6:18 am
btw, why do you persist in writing queries against a compatibility view that is deprecated and scheduled for removal. You're teaching people bad practices.
is this bad practice ? what is deprecated and scheduled for removal ?
kindly send any best practices regarding this from microsoft ? I will appreciate ?
prove it this is bad one ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 1, 2011 at 7:09 am
Thanks Gail, but the guy closed his session so I guess he'll have to re-write!
July 1, 2011 at 8:52 am
TST1 (7/1/2011)
Thanks Gail, but the guy closed his session so I guess he'll have to re-write!
The query I gave you will work even if he's closed the session, providing the plan is still in cache. The query Syed wrote won't work if the session is closed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2011 at 8:53 am
Syed Jahanzaib Bin hassan (7/1/2011)
btw, why do you persist in writing queries against a compatibility view that is deprecated and scheduled for removal. You're teaching people bad practices.
is this bad practice ? what is deprecated and scheduled for removal ?
Yes, it's bad practice. Sysprocesses is deprecated, it's a compatibility view solely for old code written for SQL 2000
prove it this is bad one ?
Certainly
http://msdn.microsoft.com/en-us/library/ms179881.aspx
sys.sysprocesses (Transact-SQL)
SQL Server 2008 R2
Contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name.
Important noteImportant
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2011 at 10:32 am
Yes, it's bad practice. Sysprocesses is deprecated, it's a compatibility view solely for old code written for SQL 2000
Practice and Recommendation are 2 different things,Here we are providing solution,that is not war for deprecated and removal ,your point is valid for the environment of development of an application not to find the solution of an issue or problem
What microsoft mentioned in your link, I think you didnt read the full para
Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Microsoft didnt say anything regarding Administration,Why DBA cant use this feature ?
@Gila you can debate better than me when we in the class of SQL Server deprecated objects but I emphasis on the solution and practical talking
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 1, 2011 at 10:38 am
Oh for crying out loud....
Yes I absolutely did read the entire paragraph. Are you telling me that when DBAs write scripts they are not developing those scripts? What are they doing then? Administering the scripts? Conjuring them from thin air?
That view WILL go away in a future version, maybe even the next one. There are replacements for it that view that are fully documented, much better laid out, much clearer column names and those have been around since SQL 2005. There is no reason to be using views that are only included so that code written for SQL 2000 won't break.
Admins need to be familiar with the new DMV and need to note the deprecation so that when MS does remove those old views (and they will) the admins won't be left with all of their admin script breaking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2011 at 10:51 am
Syed Jahanzaib Bin hassan (7/1/2011)
Yes, it's bad practice. Sysprocesses is deprecated, it's a compatibility view solely for old code written for SQL 2000
Practice and Recommendation are 2 different things,Here we are providing solution,that is not war for deprecated and removal ,your point is valid for the environment of development of an application not to find the solution of an issue or problem ....
I must be misunderstanding you. I've never before seen someone recommend hypocrisy before without it being essentially sarcastic.
Are you really saying "recommend things that you don't do" or "practice things that you recommend against"? That's what that looks like.
So, am I misunderstanding you, or is that really what you mean?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 10:52 am
My 2 bits
Admins most certainly need to be familiar with the catalog views and the DMVs. The new DMVs that were designed to replace sysprocesses are many times better. The only reason sysprocesses has yet to be removed is that it provides one piece of functionality that does not yet exist in the replacement DMVs. MS is working on that one feature (http://connect.microsoft.com/SQLServer/feedback/details/257502/deprecation-of-sysprocesses-dmvs-doesnt-fully-replace-all-columns ) in theory. IMHO, that does not prevent the recoding of admin scripts to use the DMVs. Nor should that pre-empt somebody from learning the new structures.
Personally, I would replace sysprocesses where possible. Backwards compat to 2000 is not enough of a reason for me.
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
July 1, 2011 at 11:14 am
Syed,
Here is an article for your review. It will give you the information you need to determine which of your stored queries should be updated:
Mapping System Tables to System Views (Transact-SQL)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 11:20 am
TST1 (7/1/2011)
Thanks Gail, but the guy closed his session so I guess he'll have to re-write!
I did the same thing myself a couple of weeks back. This little query[/url] saved my bacon. Try it out.
"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
July 1, 2011 at 11:28 am
Grant Fritchey (7/1/2011)
TST1 (7/1/2011)
Thanks Gail, but the guy closed his session so I guess he'll have to re-write!I did the same thing myself a couple of weeks back. This little query[/url] saved my bacon. Try it out.
yep grant i have read yours last week only.i thought to post it yours. You did it.:-)
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply