Hi All.......
I want to get all the queries ran against a SQL database during a certain time frame.
Right now, I have TOP 50, TOP 1000.........
But I want to view all the queries that were performed against a SQL database . Is there a way to get that detail ?
You cannot, doing so would require much more overhead than would be desired from a 24x7 monitoring solution. To do this, you'd be best off with something like Profiler (or Extended Events), which is/are a free native SQL Server solution.
Ok Thank you!!
By design SQL PI (for example) collects the top 50, 1000, etc rows in each collection. As Dave mentioned, we have limits on the amount of rows in the collections to limit the overhead used. Most users that we have consulted with are primarily interested in the only most active queries and not all of the quick very short duration queries.
This also occurs in other areas of the product such as the SQL Server databases dashboard where by default we collect 200 rows. These limits can be adjusted (e.g. KB 215618).
For non-PI collections, you can find the maximum number of row settings in the database agent log collection summary area.
For example:
colDisplayName=Top SQLs
colName=DBSS_Top_SQLs
colMaxRows=200
colEnable=0
colTimeout=180
colDescription=Retrieves top SQLs.
colFrequencyHigh=900
colFrequencyVSQL=180
colFrequencyLow=3600
colFrequencyFast=120
