SQL 2005

Question

Post reply

SQL 2005

balasach82

SSCrazy Eights

Points: 9653
More actions
June 18, 2010 at 4:30 am

#234062

How to Capture all Queries that is run against a DB?
I dont require the Profiler option.
Any answer (ex: DMV ) which is workable is fine.

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

How about a server side trace ?

Why is profiler no good ?

Clear Sky SQL
My Blog[/url]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Do you want to capture for a particular period of time ?

"Keep Trying"

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 3

Thanks for all the immediate response.

Profiler is resource intensive. If there are no other viable options, then Profiler can be used. yes i need the data during the prod hours i.e. 8hrs daily

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

A server side trace is (very ?) light

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Clear Sky SQL
My Blog[/url]

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

Thanks Dave. I saw the post and the script, Will try it in test.. still do you have any other options. I think there are many DMV's from which the queries can be captured and they need to be run as a job at regular intervals..

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

There are DMV's that may show you the queries that have been executed and still in cache.

The obvious issue is that not all the queries may be in the cache when you run your summarisation query.

They are not designed for the purpose you want, tracing is.

Clear Sky SQL
My Blog[/url]

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 7

Thanks a ton. Certainly would try the Trace option. Presently am trying the below method for testing:

Using a job which queries the sys.sysprocesses to get the query text and scheduled to run it every minute. As i said, it is in test server

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 8

A server side trace is the best method. Just limit the number of events and columns you're capturing and you won't even see the effects of it running, even on production servers that are suffering. Make sure you output to file, not to a database. It's just more efficient and further reduces the load.

Once you have the data, you can load it into a database and then run reports against it, or, still use the Profiler gui to read the file, without connecting to or affecting the production database. The nice thing about the Profiler gui is that you can combine the data collected from the server side trace with performance monitor data to get a very powerful troubleshooting tool.

"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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

As other posters have pointed out server side trace is the best way and certainly better than querying sysprocess every minute. My question is why do you want all the queries being run against your DB. Are you looking for anything specific or are you facing issues.

"Keep Trying"

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 10

I need to determine the Number of Transactions a DB faces in a single day.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 11

balasach82 (6/21/2010)
I need to determine the Number of Transactions a DB faces in a single day.

If you just need a transaction count, there are performance monitor counters that will give you that information, easier.

"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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 12

Thats a Lightning quick reply. My requirements are to get the No. of TXN count as well as the corresponding queries. Initially, even if i get the TXN count, it would be good. Yes, I tried using the transactions/Sec counter using perfmon and saved it in a CSV file. But i could not understand. On what basis the CSV file is getting written. Does all the SELECT, UPDATE, DELETE etc gets reflected in that file?

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 13

balasach82 (6/21/2010)
Thats a Lightning quick reply. My requirements are to get the No. of TXN count as well as the corresponding queries. Initially, even if i get the TXN count, it would be good. Yes, I tried using the transactions/Sec counter using perfmon and saved it in a CSV file. But i could not understand. On what basis the CSV file is getting written. Does all the SELECT, UPDATE, DELETE etc gets reflected in that file?

It depends on how frequently you're updating it really. That's set within perfmon.

If you do need to correlate to the procedures, then you do need to use trace events.

"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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 14

...................SQLServer:Databases(DBNAME)\Transactions/sec"

"06/17/2010 01:16:08.825"," "

"06/17/2010 01:16:28.828","0"

"06/17/2010 01:16:48.830","0"

"06/17/2010 01:17:08.833","0"

"06/17/2010 01:17:28.835","0"

"06/17/2010 01:17:48.838","0"

This is an example. How to interpret this. I ran a Select statement, then an Insert. When i ran the Select query, a line was added to the csv file, then when i ran the same query, no additional line was added. How to explain this? Actually, how does the Transactions/sec counter works? As said, this part of gettting count is more important and then only the query capture..