Hi All,
Please help me in getting the below information. I prefer T-SQL statements over GUI dashboard reports
Actually I am an Oracle DBA, but started with managing MS SQL recently.
November 1, 2021 at 4:43 pm
Sounds like things I would have used Trace Files for. But I hear Extended Events have more functionality. I don't have much need for either these days. so can't help much on E.E.
November 1, 2021 at 4:56 pm
My opinion - I wouldn't reinvent the wheel. There are a LOT of good monitoring tools out there and I would just use one of those.
The good SQL monitoring tools will provide that information for you out of the box along with a lot more information. Point number 2 is a big one as tracking a particular query by its ID is only good while that session is active. If I am using session ID 55 and then I finish my query and close the session, session ID 55 will have no information in the instance OR someone else may have grabbed session ID 55 and the query they run could be drastically different than mine. Using Extended Events you could find the query run at a specific time by a specific ID, but you are sifting through a lot of data and it will use a lot of disk as SQL isn't capturing this data automatically for you. And depending on how you configured the XE (extended event), you may end up having the data get too old and disappear off the end of the logs.
Now, a good SQL monitoring tool will allow you to configure the number of days of data you want to capture as well as send out alerts when certain things happen. Like if query XYZ starts running a lot slower than it did previously, you should get an alert. or a backup fails, you get an alert. Once you get the monitoring tool configured well, you can go to the dashboard and view the relevant metrics and work off of those.
I personally would not reinvent the wheel; I would invest in a monitoring solution.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 2, 2021 at 10:48 am
Thanks a lot @homebrew01 for pointing out Extended events for me to look into it. I will definitely explore the feature
November 2, 2021 at 11:07 am
Thanks Mr. Bran for your detailed explanation. You have rightly suggested to use a good monitoring tool. I would be glad if you can advise me with some good tools.
Actually the SQL ID that I was referring to was based on a concept in ORACLE. You see Oracle has a concept of AWR (Automatic Workload Repository) that captures all performance related statistics of any given Query based on periodic snapshots, and maintains it for a defined period of time. This allows you to analyze any SQL Query based on its ID and get relevant information on the historical performance data of that Query. You can have a detailed view on a Query's historical data like No. of Executions, Elapsed time per execution, CPU time. IO time, wait events etc. I am actually looking for the same in SQL Server. I am sure it has, but a bit confused where to start looking at.
Sounds like you will be interested in the Query Store.
Please take a look here: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2021 at 7:54 pm
Search this site for "query performance" or sys.dm_exec_query_stats.
There are a wealth of queries that will tell you what you need.
This one is a start.
select top 20
stat.execution_count as execution_count,
stat.total_logical_reads as total_logical_read,
stat.total_worker_time as total_CPU,
cache.objtype,
req.text,
'1 Execution count' as typeOf,
row_number() OVER (ORDER BY stat.execution_count desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
--Change this to order by whatever column is relevant.
order by execution_count desc
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 2, 2021 at 8:53 pm
I agree with both Phil and Michael. Phil's comment about query store sounds like it is similar to Oracle's AWR. Or so is my my understanding of query store.
And Michael has a good script there for looking at query performance from the plan cache. Only downside is that the plan cache does end up dropping query plans if it runs out of memory for them or if the instance is rebooted.
As for monitoring tools, I use RedGate SQL Monitor and like it, but there are other ones out there too. Idera, Quest, and RedGate are the big three that come to my mind. I have demo'ed Idera's and Quest's tool and I do like them, but they have always been outside what the company will budget for as you are paying per instance. RedGate is a nicer licensing model as it is per server instead of per instance. So if you have 10 SQL instances on ServerA, you only need to pay for ServerA with RedGate. YOu want to add more instances, no change in licensing with RedGate UNLESS you add another server. Idera and Quest both charge per instance, but if you only have a small number of instances, that model may end up being cheaper or the same as RedGate.
I would recommend you demo each tool and see which one suits your needs as the tools are not created equal.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 5, 2021 at 3:05 pm
Thanks a lot Phil, Michael and Bran for your incredible help and guidance. You guys have very well understood all my concerns and provided me exactly what I needed.
@Phil - Query Store is exactly what I was looking for in SQL Server that provides the same abilities as of AWR in Oracle. Thank you
@michael-2 - You have given me a great query to start with looking into the current or recent SQLs which is very similar to what we have in Oracle v$sqlarea view. Thank you
@Bran - Thanks for your constant help since the beginning of this thread. I will start with RedGate to explore it, as it sounds cost effective for our SQL server implementation.
November 5, 2021 at 3:13 pm
Thanks a lot Phil, Michael and Bran for your incredible help and guidance. You guys have very well understood all my concerns and provided me exactly what I needed.
@Phil - Query Store is exactly what I was looking for in SQL Server that provides the same abilities as of AWR in Oracle. Thank you
@michael-2 - You have given me a great query to start with looking into the current or recent SQLs which is very similar to what we have in Oracle v$sqlarea view. Thank you
@Bran - Thanks for your constant help since the beginning of this thread. I will start with RedGate to explore it, as it sounds cost effective for our SQL server implementation.
Any time! That's what we are here for!
I have a different spin on monitoring though. To me, all of the monitors available only tell you what happened. And none of them provide any real direction on how to fix what happened.
I use monitoring to validate what I already knew or suspected. And, it gives me the ability to make pretty pictures and reports to give to the powers that be!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 6, 2021 at 8:52 pm
I whole-heartedly agree Michael. Collecting data and knowing what to do with it are two very different things.
I'll also state that while extended events can be useful, it's a total pain in the ass if for no other reason than having to write and XML parser to save the data in a table if that's what you want. What's really stupid about it is that the raw data is stored in a nice, compressed, but proprietary format. There's no reason why MS couldn't have provided an easier way other than XML to automate saving the data.
Getting back to the subject with an emphasis on NOT reinventing the wheel, you might want to take a look at the following because, especially if you review the code and especially if you take the time to read about what it does, you can do some really neat things with the various embedded tools and it will also frequently provide the execution plan and a URL for where you can find out more about a problem in other cases.
Here's the link and the kit isn't just remarkable in all that it does, it's absolutely free and you don't have to sign up for squat if you don't want to. I DON'T recommend not signing up but to each there own. Signing up costs you nothing and he doesn't sell your info.
https://www.brentozar.com/first-aid/
His weekly posts and his 'tubes are also incredibly worthwhile. Not to turn this into an ad but his training is incredible, as well. Some of the training is occasionally free or seriously "on sale" but even the paid training is incredibly worth it even if someone already thinks they know it all. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply