August 5, 2014 at 4:40 am
Hi,
What tools do you use to determine if you should tweak SQL Server configuration and optimize code route, or simply bump up your virtual resources? Can someone share a bag of Extended Events to monitor at the VPS level?
I'm a reasonably decent SQL Developer but never advanced far with DBA efforts. Especially when the mainstream went virtual. Seemed to me that all SQL Servers flexibility with managing disk and memory went out the window now that everything is 'shared', NATed, and Plesked. So I basically dropped out of the conversation and built stuff with SSIS and TSQL.
Now, I'm charged with assessing a bottleneck on a VPS Windows 2012 Standard running SQL 2012 Express. I've read that running profiler and traces are deprecated and I've looked a bit at the servers extended events on the hosted environment. I have not run anything.
My question: Does it make sense to think in terms of 'levels' in deciding what to monitor? I consider the SQL Server as a level, then the Windows Server, and finally the Virtual Level. What I'm getting at, is sure, I can monitor SQL Server with a profile tool, but it won't know SQL is on a VPS. So do I miss something?
There used to be day when we had a dedicated physical box for SQL Server. We ran traces using profiler and got good clues on how to improve performance. In todays VPS world we can use sliders to increase virtual memory and disk space. What tools do you use to determine if you should tweak SQL Server configuration and optimize code route, or simply bump up your virtual resources? Can someone share a bag of Extended Events to monitor at the VPS level?
What Extened Events at the VPS level tell me if SQL Server is struggling with the limited 1Gb virtual memory? I realize this is not a direct question but hopefully someone will point this developer in the right direction.
John
SQL 2012 Standard VPS Windows 2012 Server Standard
August 5, 2014 at 8:04 am
The tools available within SQL Server for monitoring the VM level stuff (memory, cpu, some disk counters depending on your VM) are pretty much non-existent. SQL Server and it's tools assume you're working within a machine and all the counters available through extended events and dynamic management views completely reflect that. Now, using extended events and other tools as part of standard monitoring doesn't really change that much. The stuff you did in the past, you should continue doing. If a query took 5 minutes to run, extended events will show that. If it has a bad execution plan, that will show that. If it needs an index or the statistics updated or the statement adjusted, it's all the same as it ever was. Instead, you need to add monitoring according to your hypervisor. In fact, VMWare and HyperV both make suggestions on good monitoring for SQL Server at that lower level. I'd suggest reading up on the appropriate web site for that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2014 at 12:58 pm
1) With SQL Express you can get hit by the 1GB memory limit AND the one CPU limit too.
2) File IO stall analysis can tell you how much PHYSICAL IO you are doing and how slow it is. The lower RAM you have the more physical IO you will be doing. You can also to analytics between LOGICAL IO and PHYSICAL IO to see how much less physical IO you might do if you had more RAM to cache data pages.
3) Wait stats analysis can also tell you a bit about IO issues but also much more, including potential CPU bottlenecks and memory grant issues.
4) Get sp_whoisactive to see real-time query issues.
5) Get a copy of Glenn Berry's SQL Server Diagnostic Queries and use it to find issues.
6) Profiler still works and I use it at EVERY client I work with and will continue to do so until they pry if from my cold, dead fingers. Extended events cannot touch what you can do with it at this point, especially when you throw it the free awesome-sauce that is Qure and using that (or other tools/scripts) to do aggregate trace analysis!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply