Featured Script
Data_Time_Master_Table
This Script is step 2 to see the Impact of Forced Parameterization this will populate the data in time master table which was… (read more)
(Only ONE days left to register for the class this month)
SSIS Class – Online, 12 Hours/classes
Eric Johnson’s class starts May 15 and you can register now to attend! Brought straight to your desktop, this 12-session class includes weekly business hours with Eric, LOTS of information about SSIS and how you can put it to use in your own shop. There is SO MUCH information packed into this course, it’s unbelievable. Take a look at the class sessions here. If you’re working with SSIS, or considering it, you can’t go wrong.
[Find out more] here at the class site.
Counters, Troubleshooting, Performance and More
Yesterday I asked about the counters you use, or have used, to work with SQL Server. I’m curious if there’s a difference between what everyone SAYS you should watch and what you really end up watching. Please send in your feedback here and let me know what your approach and experience has been.
Shannon: "One of the most useful counters I’ve come across is the Avg. Disk sec/Transfer under Physical Disk. It’s the counter of choice for disk because it doesn’t get faked out by the smoke and mirrors of the SAN. It starts it’s “timer” when the request is made and stops it when it gets it back. I’ve used it on both DAS and SAN’s and it works great. I also use an app I created to gather perfmon stats. It’s always running (kind of old though now.. vb.net 2003). So I can look at the history of the counters during good times and bad. "
Leif: "Batch Requests/Sec for activity
Sec/Read and Sec/Write for disk performance
Proc Cache Hit Ratio and Logical Page Reads for optimization opportunities"
Derek: "We use these as our “eyes and ears” so-to-speak with all of our production dbs and for performance tuning development projects. We don’t have a large budget to be able to throw at support and monitoring so perf mon and profiler have been excellent tools for us. We leave them running all the time. We use 3 perf mons windows and typically one profiler. What we monitor:
1 – dsStats:
% Processor Processor/Time _Total (db server)
% Processor Processor/Time _Total (for each app server)
Average Latch Wait Time SQLServer:Latches
Buffer cache hit ratio SQL Server:Buffer Manager
Lock Wait Time SQLServer:Locks
Total Latch Wait Time SQLServer:Latches
Total Server Memory SQLServer:Memory Manager
Transaction/sec SQLServer:Databases
User Connections SQLServer:General Statistics
I suppose I’m old school on disks. I like direct attached, dedicated arrays for our large production dbs. Usually it isn’t hard to justify for our large deployments. As such, we physically segment Data, Index and Log segments along with tempdb and Audit dbs onto different physical spindles which map to different logical drives. Doing this makes performance monitoring of the data segments easy.
2 – disks:
Avg. Disk Quque Length PhysicalDisk (for each logical drive)
Disk Transfers/sec Physical Disk (for each logical drive)
We’ve recently started to watch some additional stats, we’ve found Lock Requests/sec to be very useful in revealing some missing index issues (we disable auto stats due to overhead costs):
3 – additional:
Page Splits/sec SQLServer:Access Methods
TableLock Escalations/sec SQLServer:Access Methods
Processes blocked SQLServer: General Statistics
Lock Requests/sec SQLServer:Locks
Lock Waits SQLServer:Wait Statistics
4 – profiler
We use a constantly running profiler to trace long running queries; typically capturing queries running longer than 1sec.
These are the tools we use for our OLTP db servers. They have been invaluable for us and in most cases, we can proactively disgnose problems before users are often aware of them. The trick can be in learning to read all the data meaningfully."