June 28, 2016 at 8:39 am
hi,
1)There are many applications which send queries to database , and they could be unparameteris ones.
so is there any way to find them from database.
2) is ther any way to find total no of recomplication of a stored procedure?
yours sincerley
June 28, 2016 at 8:48 am
I suppose for a rough and ready answer, you could query the plan cache for statements that don't contain "@". You'd have to eyeball the results to make sure it returns the kind of queries you're looking for.
John
June 28, 2016 at 9:46 am
You've got two choices on capturing this information within SQL SErver 2012 and one more with SQL Server 2016 (and Azure SQL Database).
First, extended events. You can capture rpc_completed event to see stored procedures. You can also capture sql_batch_completed to see ad hoc queries. You can filter this capture by database, or pretty much anything reasonable. However, be prepared, this will generate a lot of data. You need to be ready to deal with that. More filtering so you're only capturing a subset of all events is better.
Next, less accurate would be to use the Dynamic Management Views, specifically sys.dm_exec_query_stats, to look at the queries that are currently in cache. You can see all the queries, whether called by a procedure or ad hoc, but only the ones that are still in cache. It will show aggregate information about the queries.
If you're working in Azure SQL Database (or SQL Server 2016), you also get QueryStore. This is a function that you turn on and it then captures all queries and all execution plans with the database, so you can retrieve the information later. The runtime data is aggregated.
I'd use one, or some combination, of these methods to answer the questions you're asking.
"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
June 29, 2016 at 4:46 pm
rajemessage 14195 (6/28/2016)
1)There are many applications which send queries to database , and they could be unparameteris ones.
How do you define the difference between parametrised and unparametrised queries?
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply