Dynamic SQL Monitoring in SQL Server

Posted on 2011-10-29
Last Modified: 2012-05-12
I  need monitoring script or tool which only monitors dynamic sql statements which may generated thru hibernate or thru various other methods.
I want to see which dynamic sql is more resource intensive.I dont want the ones from the stored procedure.Thanks
Question by:venk_r
    LVL 12

    Accepted Solution

    I don't there is any way to monitor only dynamic sql but you could configure SQL Profiler with TSQL events to monitor the statistics (Duration of execution, CPU time, I/O time etc) of your queries including dynamic sql. To limit the size of your trace captured, put appropriate filters in place like application name, database name, login name and if possible hostname etc. SQL Profiler is very easy to use tool. You could find so many articles over net which tell how to use it.
    LVL 23

    Assisted Solution

    by:Racim BOUDJAKDJI
    For RAM consumption, you can isolate ORM statement from stored procedures calls by using DMV.  For CPU and IO you can use Profiler to identify most consuming statements some of them will be dynamic based and some sp based.  Most of the ORM mappers (hibernate...) usually create inner procedure cache contention since they decrease the ability of the server to reuse plans.  
    LVL 8

    Author Closing Comment


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now