|
|
-
Posted Wednesday, April 04, 2012 12:18 AM |
In the Tuning Red Gate series I've shown you how to look at a current load on the system and how to drill down to look at historical analysis of the system. I've also shown how you can see the top queries and other information from the current status of the system. I have one more thing I can show you before we need to start fixing things and showing how that affects the data collected, historical moments in time. For example, back in Post #3 I was looking at some spikes in some of the monitored resources that were taking place a couple of weeks back in time. Once I identify a moment in time that I'm interested in, I can go back to the first page of Monitor, Global Overview, and click on the icon: 
From this you can select the date and time you're interested in. For example, I saw some serious CPU queues last week: 
This then rolls back the time for all the information that's available to the Global Overview and the drill down to the server and the SQL Server instance there. This then allows me to look at the Top Queries running at this point, sort them by CPU and identify what was potentially the query that was causing the problem right when I saw the CPU queuing 
This ability to correlate a moment in time with the information available to you in the Analysis window makes for an excellent tool to investigate your systems going backwards in time. It really makes a huge difference in your knowledge. It's not enough to know that something happened at a particular time. You need to know what it was that was occurring. Remember, the key to tuning your systems is having enough knowledge about them. I'll post more on Tuning Red Gate as soon as I can get some queries rewritten. I'm working on that.
|
-
Posted Thursday, February 23, 2012 12:00 AM |
First time connecting to these servers directly (keys to the kingdom, bwa-ha-ha-ha. oh, excuse me), so I'm going to take a look at the server properties, just to see if there are any issues there. Max memory is set, cool, first possible silly mistake clear. In fact, these look to be nicely set up. Oh, I'd like to see the ANSI Standards set by default, but it's not a big deal. The default location for database data is the F:\ drive, where I saw all the activity last time. Cool, the people maintaining the servers in our company listen, parallelism threshold is set to 35 and optimize for ad hoc is enabled. No shocks, no surprises. The basic setup is appropriate. On to the problem database. Nothing wrong in the properties. The database is in SIMPLE recovery, but I think it's a reporting system, so no worries there. Again, I'd prefer to see the ANSI settings for connections, but that's the worst thing I can see. Time to look at the queries, tables, indexes and statistics because all the information I've collected over the last several days suggests that we're not looking at a systemic problem (except possibly not enough memory), but at the traditional tuning issues. I just want to note that, I started looking at the system, not the queries. So should you when tuning your environment. I know, from the data collected through SQL Monitor, what my top poor performing queries are, and the most frequently called, etc. I'm starting with the most frequently called. I'm going to get the execution plan for this thing out of the cache (although, with the cache dumping constantly, I might not get it). And it's not there. Called 1.3 million times over the last 3 days, but it's not in cache. Wow. OK. I'll see what's in cache for this database: SELECT deqs.creation_time,
deqs.execution_count,
deqs.max_logical_reads,
deqs.max_elapsed_time,
deqs.total_logical_reads,
deqs.total_elapsed_time,
deqp.query_plan,
SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
(deqs.statement_end_offset - deqs.statement_start_offset) / 2
+ 1) AS QueryStatement
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE dest.dbid = DB_ID('Warehouse')
AND deqs.statement_end_offset > 0
AND deqs.statement_start_offset > 0
ORDER BY deqs.max_logical_reads DESC ;
And looking at the most expensive operation, we have our first bad boy: 
Multiple table scans against very large sets of data and a sort operation. a sort operation? It's an insert. Oh, I see, the table is a heap, so it's doing an insert, then sorting the data and then inserting into the primary key. First question, why isn't this a clustered index? Let's look at some more of the queries. The next one is deceiving. Here's the query plan: 
You're thinking to yourself, what's the big deal? Well, what if I told you that this thing had 8036318 reads? I know, you're looking at skinny little pipes. Know why? Table variable. Estimated number of rows = 1. Actual number of rows. well, I'm betting several more than one considering it's read 8 MILLION pages off the disk in a single execution. We have a serious and real tuning candidate. Oh, and I missed this, it's loading the table variable from a user defined function. Let me check, let me check. YES! A multi-statement table valued user defined function. And another tuning opportunity. This one's a beauty, seriously. Did I also mention that they're doing a hash against all the columns in the physical table. I'm sure that won't lead to scans of a 500,000 row table, no, not at all. OK. I lied. Of course it is. At least it's on the top part of the Loop which means the scan is only executed once. I just did a cursory check on the next several poor performers. all calling the UDF. I think I found a big tuning opportunity. At this point, I'm typing up internal emails for the company. Someone just had their baby called ugly. In addition to a series of suggested changes that we need to implement, I'm also apologizing for being such an unkind monster as to question whether that third eye & those flippers belong on such an otherwise lovely child.
|
-
Posted Wednesday, February 22, 2012 12:00 AM |
I'm drilling down into the metrics about SQL Server itself available to me in the Analysis tab of SQL Monitor to see what's up with our two problematic servers. In the previous post I'd noticed that rg-sql01 had quite a few CPU spikes. So one of the first things I want to check there is how much CPU is getting used by SQL Server itself. It's possible we're looking at some other process using up all the CPU 
Nope, It's SQL Server. I compared this to the rg-sql02 server: 
You can see that there is a more, consistently low set of CPU counters there. I clearly need to look at rg-sql01 and capture more specific data around the queries running on it to identify which ones are causing these CPU spikes. I always like to look at the Batch Requests/sec on a server, not because it's an indication of a problem, but because it gives you some idea of the load. Just how much is this server getting hit? Here are rg-sql01 and rg-sql02: 
Of the two, clearly rg-sql01 has a lot of activity. Remember though, that's all this is a measure of, activity. It doesn't suggest anything other than what it says, the number of requests coming in. But it's the kind of thing you want to know in order to understand how the system is used. Are you seeing a correlation between the number of requests and the CPU usage, or a reverse correlation, the number of requests drops as the CPU spikes? See, it's useful. Some of the details you can look at are Compilations/sec, Compilations/Batch and Recompilations/sec. These give you some idea of how the cache is getting used within the system. None of these showed anything interesting on either server. One metric that I like (even though I know it can be controversial) is the Page Life Expectancy. On the average server I expect see a series of mountains as the PLE climbs then drops due to a data load or something along those lines. That's not the case here: 
Those spikes back in January suggest that the servers weren't really being used much. The PLE on the rg-sql01 seems to be somewhat consistent growing to 3 hours or so then dropping, but the rg-sql02 PLE looks like it might be all over the map. Instead of continuing to look at this high level gathering data view, I'm going to drill down on rg-sql02 and see what it's done for the last week: 
And now we begin to see where we might have an issue. Memory on this system is getting flushed every 1/2 hour or so. I'm going to check another metric, scans: 
Whoa! I'm going back to the system real quick to look at some disk information again for rg-sql02. Here is the average disk queue length on the server: 
and the transfers 
Right, I think I have a guess as to what's up here. We're seeing memory get flushed constantly and we're seeing lots of scans. The disks are queuing, especially that F drive, and there are lots of requests that correspond to the scans and the memory flushes. In short, we've got queries that are scanning the data, a lot, so we either have bad queries or bad indexes. I'm going back to the server overview for rg-sql02 and check the Top 10 expensive queries. I'm modifying it to show me the last 3 days and the totals, so I'm not looking at some maintenance routine that ran 10 minutes ago and is skewing the results: 
OK. I need to look into these queries that are getting executed this much. They're generating a lot of reads, but which queries are generating the most reads: 
Ow, all still going against the same database. This is where I'm going to temporarily leave SQL Monitor. What I want to do is connect up to the server, validate that the Warehouse database is using the F:\ drive (which I'll put money down it is) and then start seeing what's up with these queries. Part 1 of the Series Part 2 of the Series
|
-
Posted Monday, February 13, 2012 1:40 AM |
In the last installment, I used the SQL Monitor tool to get a snapshot view of the current state of the servers at Red Gate that are giving us trouble. That snapshot suggested some areas where I should focus some time, primarily in which queries were being called most frequently or were running the longest. But, you don't want to just run off & start tuning queries. Remember, the foundation for query tuning is the server itself. So, I want to be sure I'm not looking at some major hardware or configuration issues that I need to address first. Rather than look at the current status of the server, I'm going to look at historical data. Clicking on the Analysis tab of SQL Monitor I get a whole list of counters that I can look at. More importantly, I can look at them over a period of time. Even more importantly, I can compare past periods with current periods to see if we're looking at a progressive issue or not. 
There are counters here that will give me an indication of load, and there are counters here that will tell me specifics about that load. First, I want to just look at the load to understand where the pain points might be. Trying to drill down before you have detailed information is just bad planning. First thing I'm going to check is the CPU, just to see what's up there. I have two servers I'm interested in, so I'll show you both: 
Looking at the last 30 days for both servers, well, let's just say that the first server is about what I would expect. It has an average baseline behavior with occasional, regular, peaks. This looks like a system with a fairly steady & predictable load that probably has a nightly batch process that spikes the processor. In short, normal stuff. The points there where the CPU drops radically. that might be worth investigating further because something changed the processing on this system a lot. But the first server. It's all over the place. There's no steady CPU behavior at all. It's spike high for long periods of time. It's up, it's down. I'm really going to have to spend time looking at CPU issues on this server to try to figure out what's up. It might be other processes being shared on the server, it might be something else. Either way, I'm going to have to spend time evaluating this CPU, especially those peeks about a week ago. 
Looking at the Pages/sec, again, just a measure of load, I see that there are some peaks on the rg-sql02 server, but over all, it looks like a fairly standard load. Plus, the peaks are only up to 550 pages/sec. Remember, this isn't a performance measure, but just a load measurement, but from this, I don't think we're looking at major memory issues, but I may want to correlate these counters with the CPU counters. Again, the other server looks like there's stuff going on. The load is not at all consistent. In fact there was a point earlier in the year that looks pretty severe. Plus the spikes here are twice the size of the other system. We've got a lot more load going on here and I will probably need to drill down on memory usage on this server. 
Taking a look at the disk transfers/sec the load on both systems seems to roughly correspond to the other load indicators. Notice that drop right in the middle of the graph for rg-sql02. I wonder if the office was closed over that period or a system was down for maintenance. If I saw spikes in memory or disk that corresponded to the drip in CPU, you can assume something was using those other resources and causing a drop, but when everything goes down, it just means that the system isn't gettting used. The disk on the rg-sql01 system isn't spiking exactly the same way as the memory & cpu, so there's a good chance (chance mind you) that any performance issues might not be disk related. However, notice that huge jump at the beginning of the month. Several disks were used more than they were for the rest of the month. That's the load on the server. What about the load on SQL Server itself? Next time.
|
-
Posted Thursday, February 09, 2012 4:10 PM |
Everyone runs into performance issues at some point. Same thing goes for Red Gate software. Some of our internal systems were running into some serious bottlenecks. It just so happens that we have this nice little SQL Server monitoring tool. What if I were to, oh, I don't know, use the monitoring tool to identify the bottlenecks, figure out the causes and then apply a fix (where possible) and then start the whole thing all over again? Just a crazy thought. OK, I was asked to. This is my first time looking through these servers, so here's how I'd go about using SQL Monitor to get a quick health check, sort of like checking the vitals on a patient. First time opening up our internal SQL Monitor instance and I was greeted with this: 
Oh my. Maybe I need to get our internal guys to read my blog. Anyway, I know that there are two servers where most of the load is. I'll drill down on the first. I'm selecting the server, not the instance, by clicking on the server name. That opens up the Global Overview page for the server. The information here much more applicable to the "oh my gosh, I have a problem now" type of monitoring. 
But, looking at this, I am seeing something immediately. There are four(4) drives on the system. The C:\ has an average read time of 16.9ms, more than double the others. Is that a problem? Not sure, but it's something I'll look at. It's write time is higher too. I'll keep drilling down, first, to the unclosed alerts on the server. Now things get interesting. SQL Monitor has a number of different types of alerts, some related to error states, others to service status, and then some related to performance. Guess what I'm seeing a bunch of right here: 
Long running queries and long job durations. If you check the dates, they're all recent, within the last 24 hours. If they had just been old, uncleared alerts, I wouldn't be that concerned. But with all these, all performance related, and all in the last 24 hours, yeah, I'm concerned. At this point, I could just start responding to the Alerts. If I click on one of the the Long-running query alerts, I'll get all kinds of cool data that can help me determine why the query ran long. But, I'm not in a reactive mode here yet. I'm still gathering data, trying to understand how the server works. I have the information that we're generating a lot of performance alerts, let's sock that away for the moment. Instead, I'm going to back up and look at the Global Overview for the SQL Instance. It shows all the databases on the server and their status. Then it shows a number of basic metrics about the SQL Server instance, again for that "what's happening now" view or things. Then, down at the bottom, there is the Top 10 expensive queries list: 
This is great stuff. And no, not because I can see the top queries for the last 5 minutes, but because I can adjust that out 3 days. Now I can see where some serious pain is occurring over the last few days. Databases have been blocked out to protect the guilty. That's it for the moment. I have enough knowledge of what's going on in the system that I can start to try to figure out why the system is running slowly. But, I want to look a little more at some historical data, to understand better how this server is behaving. More next time.
|
-
Posted Tuesday, December 06, 2011 12:30 PM |
Getting started with the new SQL Test is really easy, but once you get started knowing where to go can be a little difficult. I've recorded two, quick little videos showing a couple of pieces of functionality. Please check them out. Here's how to install the sample database and what's inside it. Here's how you can create a "test" for best practices on your databases. Let me know what you think. Also, if you can, take a moment and tell me about your experiences with working within any of the various "agile" frameworks that use different flavors of test different development (TDD). I'm especially interested in hearing how you deal with the database on those projects. I'm curious how your experiences line up with mine.
|
-
Posted Tuesday, December 06, 2011 1:00 AM |
I'm not supposed to say this and I promised I'd be good, but. I AM SO JEALOUS! Here they are, the fifteen finalists for the DBA In Space contest. These people have been selected from the thousands that took part in the first phase of the contest. Congratulations everyone! David Kennaway
Michael Hirst
Joe Miller
Tim Ford
Mark Johnson
David Nicholls
Phillip Beazley
G P Van Eron
Victoria Holt
Simone Burcome
Jim Thurston
Richard Fuller
Gerald Drouin
John Kiernander Just remember, I'd be happy to impersonate you and ride the rocket in your stead. The next step is voting. That's right, you the reader, get to pick and choose amongst these finalists for who you think is the best person to be a "DBA In Space."
|
-
Posted Wednesday, November 30, 2011 2:32 AM |
I went through a period of time where I spent a lot of effort figuring out how to set up unit tests for TSQL. It wasn't easy. There are a few tools out there that help, but mostly it involves lots of programming. well, not as much as before. Thanks to the latest Down Tools Week at Red Gate a new utility has been built and released into the wild, SQL Test. Like a lot of the new tools coming out of Red Gate these days, this one is directly integrated into SSMS, which means you're working where you're comfortable and where you already have lots of tools at your disposal. After the install, when you launch SSMS and get connected, you're prompted to install the tSQLt example database. Go for it. It's a quick way to see how the tool works. I'd suggest using it. It' gives you a quick leg up. The concepts are pretty straight forward. There are a series of CLR commands that you use to configure a test and the test assertions. In between you're calling TSQL, either calls to your structure, queries, or stored procedures. They already have the one things that I always found wanting in database tests, a way to compare tables of results. I also like the ability to create a dummy copy of tables for the tests. It lets you control structures and behaviors so that the tests are more focused. One of the issues I always ran into with the other testing tools is that setting up the tests might require potentially destructive changes to the structure of the database (dropping FKs, etc.) which added lots of time and effort to setting up the tests, making testing more difficult, and therefor, less useful. Functionally, this is pretty similar to the Visual Studio tests and TSQLUnit tests that I used to use. The primary improvement over the Visual Studio tests is that I'm working in SSMS instead of Visual Studio. The primary improvement over TSQLUnit is the SQL Test interface it self. A lot of the functionality is the same, but having a sweet little tool to manage & run the tests from makes a huge difference. Oh, and don't worry. You can still run these tests directly from TSQL too, so automation has not gone away. I'm still thinking about how I'd use this in a dev environment where I also had source control to fret. That might be another blog post right there. I'm just getting started with SQL Test, so this is the first of several blog posts & videos. Watch this space. Try the tool.
|
-
Posted Wednesday, November 16, 2011 12:10 AM |
All entries for the DBA In Space contest have to be in by Tuesday the 22nd, 12 noon GMT.
I’m so jealous of all of you who can enter this contest. Just think about it. You’re getting a chance to take a sub-orbital rocket ride. But, here’s the kicker, the chances are limited to data professionals. That’s a pretty small sub-set when you think about it. Further, you have to gotten the answers to the quiz questions correct, which only takes a little bit of honest research, but come on. That further limits the result set. You’ve really got an excellent shot at this (and the jealousy rears it’s ugly head again). If you haven’t finished your entry, go on over to the link and get it taken care of. There’s really no reason to not do it.
Oh, and by the way, if you’re one of those (I’d say crazy) people who don’t want to ride the rocket, you can take the prize in cash. Although I’d be mighty disappointed in you if you did.
|
-
Posted Tuesday, October 18, 2011 8:03 AM |
This went out in the DBA newsletter from Red Gate, but I enjoyed writing it so much, I thought I'd share it to a wider audience: I grew up watching the US space program. I watched men walk on the moon for the first time in 1969, when I was only six years old. From that moment on, I dreamed of going into space. I studied aeronautics and tried to get into the Air Force Academy, all in preparation for my long career as an astronaut. Clearly, that didn't quite work out for me. But it sure could for you. At Red Gate, we're running a new contest: DBA in Space. The prize is a sub-orbital flight. When I first got word of this contest, my immediate response was, "And you need me to go right away and do a test flight? Excellent!" No, no test flight needed, plus I was pretty low on the list of volunteers. "That's OK, I'll just enter." Then I was told that, as a Red Gate employee, I couldn't win. My next response was, "I quit".eventually, I was talked down off the ledge, and agreed to help make this special for some other DBA. Many (most?) of us are science fiction fans, either the soft science of Star Trek and Star Wars, or the hard science of Niven and Pournelle, or Allen Steele. We watched the Shuttles go up and land. We've been dreaming of our own trips into orbit and our vacation-home on the Moon for a long, long time. All that might not arrive on schedule, but you've got a shot at breaking clear of the atmosphere. The first stage is a video quiz, starring Brad McGehee, and it's live at www.DBAinSpace.com now. Go for it. Good luck and God speed!
|
-
Posted Monday, October 03, 2011 1:07 AM |
Out of the box SQL Monitor has a number of alerts configured for you. You can just install the software, set up an SMTP account for it and walk away. Suddenly, you'll start receiving emails from Monitor because some of your systems will hit an alert threshold. Win right? Then you notice that you're getting lots of email. Suddenly, not a win. So you turn off alerting. oops. Please don't. Instead, adjust the alerts so that they are right for you and your system. This video I recorded shows how to walk through the alerts and configure them for your system because you want to reduce the noise and increase the signal so that alerts are helping you, not making you crazy.
|
-
Posted Monday, September 26, 2011 3:00 AM |
SQL Azure has no scheduling capabilities at all. If you want to schedule something like, oh, I don't know, a backup, you need to use SQL Agent or some other utility. In order to make that work, you need to use a command line. I've recorded a video on how I set up a SQL Agent job to run PowerShell scripts to backup my SQL Azure database. Check it out. For an even better presentation on how to do this, go over to TJay Belt's blog and read several of his posts. He lays it out much better than my videos.
|
-
Posted Friday, September 23, 2011 2:18 AM |
 I've been using Google+ quite a lot over the last few weeks. I've been most impressed with the Hangouts that you can do there. You get 10 people together and, if they have cameras and microphones, you can have real conversations as opposed to online chats through typing.
They recently came out with a whole slew of new functionality for the Hangouts, Hangouts with extras, which includes, sharing documents, writing spaces, and drawing spaces. Further, you can share your desktop. DING DING DING! We have a winner! Andy Leonard (blog|g+) started a Hangout right after the stuff became available and he, Ken Watson (blog|g+) & I played with some of the new technology, especially the desktop sharing. While the three of us played with our cool new toys, we were talking (remember, it's all about conversations) and we had a small idea come out of the chat. Why not host a quick Hangout for training? In fact, why not host a quick & small Hangout for training on small, focused topics, right away? Andy coined it as MicroTraining and immediately launched a hashtag on Twitter for it, #microT. He immediately scheduled a Hangout and filled the place. Read about it here. I just held my first #microT Hangout this morning. I'm extremely happy with the outcome. I immediately had 9 people fill the place up. I did a quick run through on half the slides I plan on presenting for Connections in a few weeks. It went well. I had a lot of interaction from the people attending, but, unfortunately, most of it was through chat. It seems lots of companies let people connect up, but they don't let them have a microphone. Lobby for those guys. You'll just be talking to your computer. I do it all the time, even when I'm not presenting.  Anyway, the functionality of the sharing was excellent. It just works. The ability to directly interact, especially on camera & microphone, with the people in the Hangout is wonderful. It's really almost as good as presenting in person. I also liked the short form that Andy & I worked out. As you can see from a few of the comments here, it seemed to go over well too. The one complaint that everyone has is the limit on 10 people (9 + me). I've come round from my original thinking and I believe now that this is a good thing. But you need to be able to share wider too. Google has also released word of a different Hangout called Hangout On Air. But if you read the documentation, it's only going to be available to people selected by Google. Let's hope that's just for the beta. Otherwise this will lose a small piece of its appeal. But only a small piece, it's still very attractive, at least to me as someone who wants to be able to present more information and get more interaction with people. Thanks everyone for attending. I'll do more. Watch for me on Google+
|
-
Posted Wednesday, September 21, 2011 3:11 AM |
Our new tool, SQL Azure Backup, is constantly undergoing changes just as SQL Azure itself does. Here's a link to a video I've done demoing our tool and talking about the importance of backups, even in the cloud.
|
-
Posted Tuesday, September 20, 2011 3:23 AM |
Silly me. I thought it would be easy. I grab a copy of the latest SQL Azure Backup and I show how to use the new import/export routine in SQL Azure. Ha! NOTE: These difficulties are all on the Microsoft side of things. Our nifty little tool, SQL Azure Backup, works great. after you get things configured correctly in Azure. Read through to the bottom for the "rest of the story." You have to have a Storage Account Cool. That's easy. Log into the Windows Azure Platform Manager. On the home page you can find out exactly how to do set up a storage account. 
And it's silly simple. You navigate to the "Hosted Services, Storage Accounts & CDN" and there's a listing of various services that you've set up. I found you can right click the "Storage Accounts" in the explorer part of the window and select new. Easier still, sitting right on top of the Platform Manager is a bunch of buttons. The first set is exactly what you need: 
You click on the "New Storage Account" button and you get taken to the "Create a New Storage Account" window. Simple stuff. You define your subscription, mine is the ScaryDBA. Then you define a URL, a lower case name, that's going to be where the blobs get stored (maybe, more on that in a minute) and you pick an affinity group. The choices there are varied depending on where you are on the planet, etc. I didn't much care since I'm mainly playing, so I was fine with anywhere in the US. 
You click OK and it takes a little while but you end up with a Storage Account. So far, so simple. The storage account has Access Keys that you'll need to use to set up your bacpac import/export. Again, quick & simple to access. Right click on your new storage account and there's a context menu to get your keys: 
Done. Now, the next thing you have to have to get the bacpac going in SQL Backup is the container. Basically just a folder where the blobs are going to be stored. I'm in the Platform Manager and I'm looking. There's no nifty little button. There's no context menu. I can't see any mechanism for creating a container. Time hit the books. F What? F#? F Something! Upon hitting the documentation I quickly realize I'm slightly over my head. Remember, I've been a database developer/DBA/Data Architect for the last 10 years. Yeah, I used to do a lot of C# coding & some Java and VB.NET & VB, but that was a while ago. Now I'm suddenly looking at F# code. I didn't even realize that C# was old. When did D# & E# come out? This part was not so easy. First, I found I didn't have Visual Studio even installed. Got that done. Started up a C# Cloud project and then found I needed multiple DLL's downloaded there. Yay! Got that done. Just the installs were taking all sorts of time. Finally, it was all done. I had to search around to find a suitable bit of code to perform the action and found a good example here at this link. Now I can finally run a backup using the Azure Import/Export option with SQL Azure Backup. oh, just got an email from the developer. He's updated SQL Azure Backup. what's that? It'll create a container for you because that's a pain for most people? I'm posting this blog post anyway.
|
|
|