SQL Server with Mr. Denny


February 12, 2014  2:00 PM

How Much Is That Query Costing?



Posted by: Denny Cherry
Database Administration, Execution Plans, Indexing, SQL, SQL Server

When we think about the cost of the query we typically think about the cost which SQL Server assigns to execute the query. However when we look at performance tuning queries there is another cost that we need to think about. This additional cost is the cost to the company while the query is running. As we start looking into these costs we can start to see just how important it is to performance tune every query within our applications.

Say we have a query which takes 0.5 seconds to run. We might think that is pretty good and we don’t need to do any more performance tuning on it. Now lets assume that this query is the customer lookup query that every one of our customer service agents uses every time that a customer calls into the call center. As a large company we have on average 1500 customer service reps on the phone at any time (working three shifts), and they each take 10-15 calls per hour. That query is now being run 18000 times per hour (give or take). Assuming that our customer service reps make $10 per hour it is costing us ~9000 seconds per shift or ~7.5 hours of staff time per day. That’s a cost of $75 per day just to run this specific query. Multiply this out for a year and that’s ~$27,375 per year, just to watch the query run. By tuning the query so that it runs within 0.1 seconds we can cut these costs down to ~$5,475 per year. That is a decent amount of financial savings to the company for just one query.

As we start looking at the rest of the queries in the application and we start adding up the costs for those queries suddenly we can easily be looking at $100,000 a year or more is employee costs just to use the application.

Wouldn’t it be nice to be able to tell management that you’ve just saved the company $100,000 a year just by adding a couple of new queries to the database?

This is why we need to do performance tuning.

Denny

February 8, 2014  1:10 AM

Recommended reading from mrdenny for February 07, 2014



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: grengifo2011 also known as Gonzalo Rengifo

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


February 5, 2014  2:00 PM

I Want This Project Done Properly, Done Quickly, and Done Cheap



Posted by: Denny Cherry
Consulting, Social Commentary, SQL, SQL Server

This is the standard requirement that I get from a lot of clients. Sadly those three requests aren’t going to go well together. In IT (and really in anything) you have to pick two:

  • Done Properly
  • Done Fast
  • Done Cheap

If you want something done properly and done fast it isn’t going to be cheap. If you want it done properly and cheaply it won’t be done quickly. If you want it done fast and cheap then odds are it won’t be done properly.

Doing good work takes money to get the right resources to get the project done correctly. Getting the project done quickly requires that you get someone who has done this sort of thing before. When it comes to large complex systems or with cutting edge platforms your pool of potential people to work on the project gets very small very quickly. The smaller the pool of people who can work on something the more those people are going to charge to get the work done. The reason for this is simple, with skill and practice comes higher rates.

When working on projects that require that you bring in outside help from consultants to need to pick the two of those three things above that you want. If you’ve gone through this thought exercise and you still want all three you need to rethink the thought exercise. And to make the whole process worse you’ve only really got two of those items to choose between. Do you want the project done quickly, or do you want it done cheaply. The reason for this is that having the project done incorrectly isn’t an option.

When I say quickly I don’t mean actually quickly based on a calendar. I mean on what ever “short” time schedule the project manager has some up with. If the project takes 6 months and it needs to be done in exactly 6 months no matter what that means it’s getting done quickly. If however the project takes 6 months, but you don’t need it done for the next 18 months you can probably get people who are a little less expensive to work on it because there is extra time built in to the schedule so things can run a little slower.

When putting those projects together keep these things in mind as talking to your consultants so that you are prepared for the answers that they give you.

Denny


February 1, 2014  6:08 AM

Recommended reading from mrdenny for January 31, 2014



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: SQLBalls also known as Bradley Ball

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


January 29, 2014  10:42 PM

eForensics Magazine SQL Server Issue



Posted by: Denny Cherry
SQL, SQL Server

The digital magazine “eForensics Magazine” has put together their 1st issue talking specifically about SQL Server security.  There are articles written by a bunch of SQL Server experts including the two articles which I have in the issue.  There’s a free teaser of the issue which you can download as well (it’s down at the bottom of the page) before you buy.

Denny


January 29, 2014  2:00 PM

Log Shipping Databases and Permissions Problems



Posted by: Denny Cherry
Attaching Database, Data Security, Database, Database Administration, Error 3013, Error 3119, Error 4319, Full Recovery Model, Log Shipping, Recovery Model, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Recently I was doing log shipping for a client in preparation to move their databases from one data center to another, when I was asked to change the drive that one of the target databases was being restored to.

No problem, I’ll just use ALTER DATABASE … MODIFY FILE (name=’xxx’, filename=’yyy’) to change the files, stop SQL, move the database files, and restart no problem.

Well apparently when doing this I managed to not reset the permissions correctly but only on the ndf file. Because SQL was able to access the MDF the database came up on NORECOVERY waiting for more logs to be restored. But when the next transaction log file was attempting to be restored, the error message that I got was not all that helpful to the problem at hand.

Msg 4319, Level 16, State 5, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘xxxxxxx’. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I tried restoring the older log backup and that didn’t help any. So I went and looked at the permissions again, and apparently SQL didn’t have write access to the NDF file so it wasn’t able to roll the logs forward. Thankfully I noticed the problem right away when I manually did the next log restore otherwise this could have been a nightmare to track down given that oh so helpful error message.

Denny


January 23, 2014  9:56 PM

SQL PASS BA Conference 2014 Discount Code



Posted by: Denny Cherry
SQL PASS, SQL PASS BA CON 2014, SQL Server

I’m pleased to say that I’ve been able to get a discount code setup for all my clients and blog post readers to get you $150 off the cost of entry to the SQL PASS BA Conference. To use the code simply register for a new registration and enter the discount code “BABN9H” in the discount code field. And that’s it. You save $150 off the registration fee for the PASS BA Conference.

If you don’t know what the BA Conference is, it is the premiere Business Analytics / Business Intelligence conference and unlike most conferences it is put on by data professionals not venders who have an agenda to push on you.

Denny


January 22, 2014  7:00 PM

Changing your Password on Windows 2012 via RDP



Posted by: Denny Cherry
Change Password, Data Security, Microsoft Windows, Security, SQL, SQL Server, Windows 2012, Windows 2012 R2

I ran into a problem at a client recently that I’m shocked that I haven’t run across before. I couldn’t figure out how to change my domain password when connected to their servers via remote desktop. You see the problem is that 99% of the time when I’m working with a client, either at their site or from my home I’m using my own machines to work, and simply connecting to their servers via remote desktop. This means that when I press control+alt+delete to get the change password option that option is for my local machine not the remote machine. As there’s no start menu on Windows 2012 the good old “Windows Security” option isn’t there when connected remotely. All the official help docs tell you to simply press control+atl+delete to get the change password option. Well that doesn’t work very well via RDP.

Thankfully you can simply press control+alt+end instead and that will bring up the same menu as control+alt+delete but on the remote machine so that you can actually change your password and stop using the crappy default password that the help desk setup for you.

If you run across this, hopefully you’ll remember this little trick.

Denny


January 17, 2014  2:00 PM

Recommended reading from mrdenny for January 17, 2014



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


January 15, 2014  2:00 PM

Fixing TempDB database problems when starting SQL using a minimum config



Posted by: Denny Cherry
Data Loss, Database, Database Administration, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, T/SQL, TempDB

So the other day I typo’ed something. Usually this isn’t that big a deal, but in this case when fixing tempdb for a customer I told SQL to put two logical files into the same physical file. I was able to get it to let me do this because I first added the files, then realized that I had put them on the wrong drive so I just changed them using an ALTER DATABASE tempdb MODIFY FILE command to move them to the correct drive. Then I had the client restart the SQL Server later when it wasn’t being used.

When SQL tried to come online however there were some lovely errors in the log and SQL wouldn’t come up.

2013-12-08 13:59:38.23 spid9s Error: 5161, Severity: 16, State: 1.
2013-12-08 13:59:38.23 spid9s An unexpected file id was encountered. File id 3 was expected but 9 was read from “E:\TempDBs\tempdev2.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
2013-12-08 13:59:38.24 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

This was a problem.

So I fired up the SQL Server using the “-c -f -m” parameters. This bring the SQL Server up as a command line application (-c), using a minimal config (-f) and in single user mode (-m). However when I tried to run the ALTER DATABASE tempdb MODIFY FILE commands all I was getting was an error about the tempdb not having the files that I was looking for. Apparently when you start SQL Server using a minimal config it only uses the first two tempdb database files, so I wasn’t able to make changes to the files.

Thankfully there’s another way to fix this sort of problem, but it isn’t for everyone. I had to go and edit the master.sys.master_files catalog view manually. Now thankfully it’s a lot easier then it seems, it’s just update statements. But you are using just T-SQL, probably in sqlcmd (at least that is what I was using) to make the changes. Now normally you can’t change catalog views manually using T-SQL. To make these changes you have to connect to the database engine using the DAC or the Dedicated Administrative Connection. Now if you are in a situation where you have to edit this stuff be VERY careful. If you run an update statement without a WHERE clause in here you’ll destroy every database on the system. But for those brave enough to try it, it’ll get the job done.

After running a bunch of update statements for files 3-9 on database_id 2 (which is the tempdb database on every SQL Server out there) a quick restart of the SQL Server service and the customer was back up and running again.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: