Home > Query, SQL > BISQL#6:Identifying Running Query and Last Modified Query

BISQL#6:Identifying Running Query and Last Modified Query

Hi Friends,

Just for your knowledge Windows 8 is going to launch this year,So you can directly go to This link and grab the experience of it !!

  • Today we are going through two Query which may help you in your development section .

Query 1 :First one is identifying or watching currently  running query on SQL Server

When large number of user working on the same database with N number of user login and password and we are getting low response from SQL server box we you following query to Identifying Running Query

--IDENTIFY CURRENTLY RUNNING QUERY ON THE sql SERVER

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 

Output of above query is :

image

Query 2 : Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that

--Following script will provide name of all the 
--stored procedure which were created in last 7 days,
--they may or may not be modified after that.
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7
----Change 7 to any other day value

image

Some time you many have to use following query instead of above,which have same output but different tracking points

 --stored procedure was created but never
 --modified afterwards modified date and create date
 --for that stored procedure are same.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
----Change 7 to any other day value
 

Todays Quote :

When dealing with people, remember you are not dealing with creatures of logic, but creatures of emotion …..By Dale Carnegie

Enjoy learning and keep sharing knowledge !!!

 

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 7 :ALL in One Database Hero Script !! Set–I

· BISQL # 8 : Pros and Cons of Money Data Type

· BISQL # 9 :How to Get Answers,Common Question :SQL Server FAQs eBook

· BISQL # 10 :Top 10 Things For SQL Server Performance Part – I

· BISQL # 12 :SQL Server Technical White Papers,All in one

 

Hope you will like this post on Running Query & Modified Query.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

              For More information related to BI World visit my Mentalist Blog

                             Link Resource Blog >> Daily Interesting links

                            SQL Server Mentalist >> SQL Learning Blog

                            Business Intelligence Mentalist >> BI World

                                             Connect With me on

                   | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                           Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: