SQL Server: обнаружение непараметрических запросов

Question

У меня есть клиент, у которого программирование было выполнено прошлыми разработчиками. Их код недавно стал подозрительным, и я хотел бы знать, используют ли они параметризованные запросы. Я надеялся, что смогу обнаружить непараметрические запросы через SQL Server, но я не нашел способа сделать это. Я понимаю, что не все запросы должны быть параметризованы, так как запрос может быть что-то вроде

select count(*) from Customers

Но если бы SQL Server мог мне программно сказать, какие запросы имеют какие-либо строковые константы вместо входных параметров, это было бы здорово. Кстати, регистрация всех SQL-запросов и удаление всех строк с символом @ близки, но следующий запрос будет считаться законным:

select * from Users where Username='user' and Password=@Password

Поэтому мне действительно нужно, чтобы SQL Server прочитал содержимое команды и определил, все ли входные параметры параметризованы. Спасибо.

Answer 1

На самом деле вы ищете специальные запросы (не хранимая процедура и не sp_executesql).

Вы могли бы начать с взгляда на sys.dm_exec_cached_plans DMV:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    ST.dbid = DB_ID()
    AND CP.objtype IN ( 'Adhoc')
ORDER BY
    CP.usecounts DESC

Просто помните, что Relational Engine может параметризовать простые запросы (функция, называемая простой параметризацией), поэтому вы можете иметь те же строки для

Если вы хотите повысить производительность, не кэшируя планы для специальных запросов, существует опция сервера, которая называется optimize for ad hoc workloads это сигнализирует Relational Engine, что при первом выполнении запроса => сохранить более легкую версию плана (план-заглушку).

Планирование загрязнения кэша также может быть причиной снижения производительности базы данных. Проверьте этот запрос, чтобы определить, является ли это и вашим случаем.

Answer 2

Михай указывает на текущий системный объект, dm_exec_cached_plans, (Когда я посмотрел ответ на ваш вопрос, я обнаружил, что [syscacheobjects] на самом деле не рекомендуется.) Поэтому, я действительно upvote его ответ.

Тем не менее, я все еще думаю, что это может сэкономить ваше время, чтобы проверить одну цитату после WHERE, С помощью dm_exec_cached_plansвот аналогичный запрос, используя WHERE как я дал вам раньше:

SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [text] LIKE '%WHERE%''%'
----AND [text] NOT LIKE '%sp_executesql%'  ----queries probably ok, with sp_executesql
----WHERE usecounts > 1   ----un-commenting this might also be interesting to check out. 
ORDER BY usecounts DESC;

Или просто добавив его в запрос Михая:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    CP.objtype = 'Adhoc'
    AND ST.dbid = DB_ID()
    AND ST.text LIKE '%WHERE%''%'
ORDER BY
    CP.usecounts DESC

Обратите внимание ORDER BY, построенный на мысли, что часто встречающиеся запросы вызывают частые разоблачения. Но, как вы, вероятно, уже поняли... если подозрительный код сам строит строки запроса, то уникальные значения (например, номера строк или идентификаторы клиентов или номера заказов и т. Д.) Могут создавать уникальные запросы, которые (если они были созданы без использования sp_executesql), на самом деле может быть самым важным, чтобы посмотреть, даже если они имеют usecounts = 1,

Что касается ответа на ваш вопрос ("Когда запросы SQL удаляются из этой таблицы?"): Если в кэше достаточно запросов, чтобы вызвать "нехватку памяти", старые запросы выпадают из кэша по мере появления новых запросов. войти... и все это очищается при перезапуске SQL. (См. http://www.sqlservercentral.com/Forums/Topic1375781-391-1.aspx и http://technet.microsoft.com/en-us/library/ms181055%28v=sql.105%29.aspx)

Надеюсь, это поможет...