HOWTO: SQL Server: Reporting on performance

Some resources to draw info on SQL Server memory usage, locks, fragmentation, etc. This is my favorite SQL Server tuning site: http://www.sql-server-performance.com/

SQL Server Perf Mon

SQLServer: Access Methods - Page Splits/sec: Number of page splits occurring as the result of index pages overflowing.
SQLServer: Databases - Data File Size (KB)
SQLServer: Databases - Log File Size (KB)
SQLServer: Databases - Log File Used Size (KB)
SQLServer: General Statistics - User Connections: Number of users connected to the system.
SQLServer: Locks - Average Wait Time (ms): The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
SQLServer: Locks - Number of Deadlocks/sec: Number of lock requests that resulted in a deadlock.
SQLServer: Memory Manager - Total Server Memory (KB): Total amount of dynamic memory the server is currently consuming
Many, many others…

Queries

View Blocks
SELECT spid, blocked, waittype, getdate() AS 'Time'
FROM master..sysprocesses (nolock)
WHERE blocked <> 0

View a whole lot of info on processes
SELECT *
FROM master..sysprocesses (nolock)

View process info sorted by memory usage
SELECT *
FROM master..sysprocesses (nolock)
ORDER BY memusage DESC

How to monitor SQL Server 2000 blocking

Stored Procedures (system)
Sp_lock
Sp_who
Sp_who2
Sp_monitor
sp_spaceused

Comments

Hero Write-up: Now this is Customer Service!

My best friend Scott is president/C-everything of a small northeastern Ohio manufacturing concern, KirkKey Interlock . I hadn't spoken with him for a while and wanted to see how Canton fared with the Blizzard of '08 (that's what they're calling it...not me). I say, "So what's new?" He replies that on Tuesday his primary server (which essentially runs the business) came up with lame with not one, but [a statistically improbable] *two* physical disk failures on a RAID5 hardware array. My friend attempts the fix but gives up pretty quickly after seeing some Linux nasty-grams on the boot screen. His service provider is an old college buddy who lives down in Raleigh, Cerient Technologies led by Jason Tower . Scott couldn't email out because Exchange was on the toasted server. Being creative, Scott started Treo-emailing photos of the screen. Unfortunately, Jason couldn't receive email because a storm had knocked out a lot of local hosting. [Sigh] After...

Certified or Certifiable?

As a senior technology professional, I interview a lot of candidates. I also maintain solid relationships with other folks in the community. Frequently, the topic of certifications arises: A good investment? Valuable? A clear measurement of skill? Consensus appears to draw the line related to one's seniority. If you're (for example) just out of school and looking for an instant creditability boost, by all means pursue a certification. Likely, this credential will assist you in overcoming the "junior" tag and likely land you more interviews and client roles. (Note: I'm going to use the terms senior and junior here...no offense to either. Can't think of a better one word description. I was a junior once too.) In stark contrast, the value of certifications drops off the table around the 2-3 year mark. Some in my circles even perceive certifications as a negative for the senior professional. They think, "If this guy is so solid, why is he wasting valuable...

Fast and Reliable Home Internet: Your Livelihood Depends on It

You're on yet another Zoom call and...wait what did she say? Dang it...Internet glitching again! You quickly mute your audio and video. "Kids! Get off YouTube...I'm on a call!" With everyone working and schooling from home, your Internet can't keep up. The cable company keeps claiming you're on their "super-fast Internet" but everything keeps lagging. It's all so frustrating and you just want to get your work done. It may not be the cable company's fault. Use this approach to ensure your household enjoys a super-fast, reliable Internet! Start with the Source Run a speed test. Google "speed test" . Run that test a few times on a given day. If you're not getting at least 50Mbps download and 10Mbps upload speeds, keep reading. Check with your Provider and do your Homework Reach out to your Internet provider. This may be your cable company or telephone provider. Understand your current plan: What package are you currently on? Wha...

Effective Thoughts

Search This Blog