Why is Your SQL Server Slow? All-for-one and One-for-all

I have one more thing I want to talk about regarding the conversation I had recently with my friends and former colleagues at Idera about the things that slow down SQL Server. Another “quick performance wins” you can take advantage of is segregating the types of files SQL Server uses. If you install SQL Server by just clicking NEXT until you get to the end, you end up with everything running on the same drive. Different files have different types
read more

When DMVs Cost Me My Job

Back in early 2006, I was still working at my first ever DBA job. I had only been a DBA for a little less than 3 years, but I felt like I knew a lot. I spent every night on the forums helping people with issues as a way of learning new things myself. But SQL Server 2005 was out and things were vastly different. I had taken a three day training class on updating my DBA skills to SQL
read more

T-SQL Tuesday #49: Wait For It Roundup

A huge round of applause and my thanks to all of the participants and spectators for this month’s edition of T-SQL Tuesday. I hope I didn’t keep you waiting too long for the roundup. <rimshot> Let’s take a look at this month’s participating blog posts: Fellow Certified Master (MCM) Rob Farley (blog|@rob_farley) led off with Waiting, waiting…. Rob talks about what causes SQL Server (and computers in general) to be slow and how to baseline SQL Server waits to detect
read more

T-SQL Tuesday #49: My Go-To Query For Waiting Tasks

I’m not just hosting T-SQL Tuesday, I’m a customer as well. Err, I mean participant. Topic for this month’s T-SQL Tuesday, hosted by yours truly, is … wait for it … Waits and Queues. For my donation to this little blog party, I’m contributing my favorite go-to query for waiting tasks. Do you ever get complaints that SQL Server is “slow”? Yeah, that’s a helpful report, right? when I get those kinds of reports, I like to take a quick
read more

T-SQL Tuesday #49: Topic is … Wait for It …

Have you been waiting to hear about this month’s T-SQL Tuesday topic? Here’s the announcement, right here and right now. The topic for this month’s T-SQL Tuesday is … wait for it … What? Still waiting? Well, suspend your confusion because that’s it. The topic is (queue the fanfare) Waits. As in “Why is my query waiting?”. As in sys-dot-dee-em-underscore-oh-ess-underscore-waiting-underscore-tasks. Err, I mean sys.dm_os_waiting_tasks. Give us your most interesting post involving waits (wait types, queues, DMV queries, etc) next Tuesday,
read more

Bug: sys.dm_db_session_space_usage Reporting Extremely High Tempdb Allocations

A little more than a year ago while working at Idera, I was consulted on reports from a few clients using the Idera monitoring tool SQL diagnostic manager (SQLdm). SQLdm was reporting very high numbers for internal object allocations (internal_objects_alloc_page_count) and very low numbers for internal object deallocations (internal_objects_dealloc_page_count) to the point that SQLdm showed that individual sessions were reported using more space than was available in the database. I worked with Vicky Harp (blog|@vickyharp), SQLdm dev lead and an
read more

SQL Saturday #198, Vancouver, BC Session Files

This past weekend was SQL Saturday #198 in Vancouver, B.C. It started off with a leisurely drive up to Vancouver on Friday and getting settled in to my hotel room. I was in the first slot of the day, and had a decent turnout despite the early hours. Whenever I have the first or last slot of the day (once I had both the first and the last slot of the day), I am always grateful that people get up
read more

T-SQL Tuesday #024 – Using SYSTEMRESTRICTSCHEMA Function in MDX to Query DMVs

T-SQL Tuesday #024 – Using SYSTEMRESTRICTSCHEMA Function in MDX to Query DMVs This blog entry is participating in T-SQL Tuesday #024, hosted this month by Brad Schulz (Blog). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Prox ‘n’ Funx. For my part in this month’s blog party, I’m going to write about
read more

SQL PASS Summit 2011: Session Files

SQL PASS Summit 2011: Session Files This past week was the SQL PASS Summit 2011 in Seattle, WA. I presented three sessions (regular session, lightning talk, panel discussion) plus ran a focus group on advanced certification and hosted a Birds of a Feather lunch table on disaster recovery. It was a busy, busy week. Below are the slide decks and demo files for the three presentations I made. All of these files have been uploaded to the SQL PASS Summit
read more

The Truth About NOLOCK Hints

The Truth About NOLOCK Hints There was a post this week from a well-known blogger who was investigating the NOLOCK locking hint. He says that he was asked whether a query using NOLOCK hint takes locks or not. After he responded that NOLOCK does not take any locks, the person who posed the question suggested that he think on it further. Through some testing, he came to the conclusion that a NOLOCK hint does indeed take a shared schema lock
read more