SQL Server 2005 - expensive queries

I am basically an App developer. Do not know much beyond X++. However, as Ax is maturing I felt I need to grow beyond X++. Ax is no more a tiny product, with the release of the next version of Ax this product is really going to explode.

So I started poking around in SQL Server 2005.

SQL Server hides a lot of data relating to the performance of queries. This data is retained till the SQL Server is restarted.

Take a look at the following query

SELECT TOP 20 qs.max_elapsed_time, qs.total_worker_time, qs.execution_count ,

st.text AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time DESC;

This will give you the top 20 costly queries since the SQL Server was restarted.

Also take a look at the following

SELECT SQLTEXT1.text, SQLREQUEST.session_id, SQLTEXT.text, SQLQUERY.query_plan FROM

sys.dm_exec_requests AS SQLREQUEST

CROSS APPLY sys.dm_exec_sql_text(SQLREQUEST.sql_handle) AS SQLTEXT

OUTER APPLY sys.dm_exec_query_plan(SQLREQUEST.plan_handle) AS SQLQUERY

OUTER APPLY sys.dm_exec_cursors(SQLREQUEST.SESSION_ID) AS SQLCURSOR

OUTER APPLY sys.dm_exec_sql_text(SQLCURSOR.sql_handle) AS SQLTEXT1

This will give you the currently running queries / SQL statement.

This is not the end of it. You will see a field called query_plan. Click on this field to open the query plan. Use file - save as option. In file type scroll down to 'all files'. Save the file with an extension .sqlplan. Now open this saved file using file - open. Presto you will see the complete plan used by the query with information about records sorted, returned, join used, index used, predicates and so on.

Next, do you see a statement in SQL profiler like exec sp_cursorfetch 180150699,2,1,1 . I don't know about you but I feel like ripping my arm off and throwing it on the monitor every time I see this. The bloody thing does not tell me just anything. Well I am trying to figure out how to get this information and I will let you guys know as soon as I figure this out.

Send mail to harry@systomatics.com with questions or comments about this web site.

Disclaimer: I am working with Microsoft Business Solutions. The code on this site may or may not be related to my official duties with Microsoft. I do not claim in any expertise in modules represented on this website. Essentially there is just one person doing functional specifications (in head), design specifications (in head), coding and some testing. There is no way the project on this site will be free of bugs. The projects are intended as guidelines and may god help you if you decide to implement the projects without making any changes. If you implement any project resulting into data corruption or anything like that then do not even think of suing me because a. I have already warned you and b. I don't have any money. I may or may not respond to your emails about supporting the project. I may or may not upgrade the projects to the next service pack / version.