Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arie01
Starting Member
8 Posts |
 Posted - 2013-12-18 : 10:39:54
|
| Hello, Sometimes I have a problem with my SQL server when it runs complex queries. It slows down and users are complaining about it.How can I see live which query and who is the host that running it are causing this slowness of my server? Is this information available in the buil-in reports? Which report is it? Is there another way of getting this information "on demand" when ever it is happening?Your help is very much appreciated.Thank you, |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-12-18 : 16:09:50
|
| Right-click on the database server in object explorer, then Reports -> Standard Reports, and look for performance related queries.Once you find the top queries, there are a number of tools and system views that will help you identify the bottlenecks - google and you will find many. http://technet.microsoft.com/en-us/library/ms172984.aspx |
 |
|
|
arie01
Starting Member
8 Posts |
Posted - 2013-12-18 : 17:02:20
|
| Hi James, thank you for your answer.I've been using the Performance - Top Queries by Total CPU Time, Activity - Top Sessions and Activity - Top Connections.All of these don't show what is currently going on on the server. They show queries that were recently executed or queries that run multiple times and the accumulated CPU time. Sometimes these queries were executed not too far before I checked the report but some queries from these reports are dated up to 3 or even 4 days days before I check the report. I would like to know if there is anything on the server that when ever I run it, it will show me me what's running at that time, CPU time, memory consumption and most importantly, the host that's running those resource hungry queries.Is there a tool like that?Thank you, |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-19 : 04:13:43
|
SQL Profiler will do that (its in the Programs Menu under "Microsoft SQL Server"). Bit more tricky than just using the Standard Reports  It has a template (provided by default) for long duration queries (although a short duration query which is run very often may also benefit from optimisation)There are also queries you can run (and also Hints that you can get in SSMS) which will make recommendations for adding indexes to improve queries. Best not to just implement them all! as it does need a consolidated plan, but it can be a useful tool. |
|
|
|
arie01
Starting Member
8 Posts |
Posted - 2013-12-19 : 10:55:08
|
| Hi Kristen, thank you for your answer.I’m not a DBA, I just occasionally manage our SQL server.Recently, there are many issues with it slowing down and when I run the tools I mentioned in my previous post, it didn’t give me enough information as to what’s going on with the server.I never heard of Profiler and I don’t know how to use it. However, I did a Google search and there is a lot of information about it so I’m going to see if this is the tool I was looking for.Thank you for bringing it up because as I said, I never heard of it before.I’ll post back if this is the right tool for the job for me.Again, thank you, |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2013-12-27 : 15:06:08
|
| http://connectsql.blogspot.com/2011/10/sql-server-using-sql-profiler-to.htmlIf executing query using SQL Server Management Studio then select SQL BatchCompleted as event.--------------------------http://connectsql.blogspot.com/ |
|
|
|
|
|
|
|
|
SQL server slows down by a complex query