She's a little runaway (query)

Stop that runaway query!

Have you ever noticed how SQL Server likes to just take over the CPU and memory of the box it is installed on? It just says, in it’s little SQL brain, “Gee, I think 90% of the CPU should be sufficient to run this query, and maybe I will just keep this memory in case I need it for something else.” Meanwhile, all other queries and/or applications start turning blue from lack of resources and die on the way to the query optimizer.

Are you ready to take back control of the CPU and memory and force applications or users to only use their fair share of the server’s resources? Well, another great, new feature of SQL Server 2008 is called the Resource Governor. You can set limits on how much CPU and memory can be used by creating functions that define an application name, a user name, a host name, a server role name, and so on.

You start by creating Resource Pools and assign minimum and maximum CPU and memory percentages to each pool. For example, you create a pool called Pool1 and assign it a minimum of 20% and a maximum of 30% CPU. This means that anything assigned to the pool will always have some CPU available (20%) but will never exceed 30%.

You also create Workload Groups which are assigned to specific pools. These workload groups can be assigned a high, medium or low priority within that pool. At this point, it would be fair to mention that all unclassified work is dumped into the Default Workload Group. This pool has UNLIMITED access to system resources, the reason that so many of us have seen runaway applications.

The third part is to create Classifier Functions. This defines which users, applications, roles, etc. (workloads), go into which workload groups. Now, we have control!

Just so you know, SQL Server 2008 reserves some CPU and memory for itself that is never given to any user or application. Didn’t you ever wonder how you could access a locked up server using the “SQLCMD –A” utility? There is always CPU and memory on reserve just to keep the motor running like the well oiled machine that SQL Server is. 

And if you like, I can show you how to build one of these Resource Governor contraptions right here in my MS 6231 class which runs from October 18-22 (it’s actually covered on Friday, usually). And if all else fails…

“Just hit it with a hammer!”

- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Tired of slow SQL queries?

Getting tired of those poorly performing queries or stored procedures? Getting even more tired of the phone calls that result from those poorly performing queries or stored procedures? Obviously, disconnecting your phone and huddling in the corner crying is not the answer (take it from me). Maybe what you need to do is look at the structure of your database and consider a little modification. First, a few questions.

Is at least one of your tables the size of (insert favorite Hollywood actor’s name)’s ego? And does that titanically (not a movie reference) humongous table have at least one column that could be used to divide the data into smaller chunks, like a date column in a Sales table with many months or years of data with hundreds of thousands, maybe even millions of rows? Can you add (would your budget allow) more physical disks to your SQL Server solution? And the biggest question, can you afford, or do you already have, the Enterprise edition of SQL server?

Well, if you were able to answer yes to all of those questions, it is possible that you might be able to tweak that lumbering hulk into exhibiting a few more miles per hour by making a few simple, if not inexpensive, changes.

The short version (level 000) is that you create new physical drives, define ranges of data and assign those ranges of data to different disks (or RAID 5 arrays). This is called table partitioning. Read on only if you really want to know how it is done (level 100)!

First, you must decide where to divide your data. For example, if you have about 10 million rows of Sales data for the last 5 years, you need to choose how to break that data down into smaller pieces. This decision is really based on how many physical disks you can add to your system. If you like keeping your data on RAID 5’s and you can get your hands on 5 more RAID 5 arrays,  you can divide you data into 5 parts (which just happens to nicely match 5 years of data).

So you create 5 new RAID 5 arrays. And in your database properties you create 5 new filegroups with at least one file each, one filegroup per RAID 5 array. Then, we are going to use these 5 different filegroups residing on 5 different arrays to create our partitioning strategy.

Now, we use a Transact SQL statement like this one

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)

AS RANGE RIGHT FOR VALUES ('20030101', '20040101', '20050101',

                           '20060101', '20070101');

to define the range of values for each portion of our table that will be stored separately from the rest. In this case, all sales for the year 2003, date 20030101 thru 20031231 (RANGE RIGHT starts with date 20030101 and ends before the next date, 20040101) will be assigned to the first partition in our function. Then 2004 is assigned to the next and so on. The last partition, 20070101, being the last listed, includes all subsequent dates, unless the function is later modified, which it can be.

Next, each range is assigned to a filegroup with a statement that looks like this

CREATE PARTITION SCHEME myRangePS1

AS PARTITION myRangePF1  --the name of the function we just created

TO (test1fg, test2fg, test3fg, test4fg, test5fg, test6fg); --these are our filegroups

Now, all of the data for 2003 will be stored in the first filegroup on the first RAID 5 array, the data for 2004 will be stored on the second new array, and so on. This will give you more actual disks supporting queries for a single table, reducing (theoretically) disk I/O and increasing (keep your fingers crossed) query response times. Yeah, I know I mentioned 6 filegroups in my scheme but I need to leave at least one mystery hanging out there for you to explore on your own or read on http://msdn.microsoft.com (or learn about in my class!!)

If you really need more info, and you want me to give it to you (because who wants to take advantage of all that great free info on the internet?), connect to me on the LinkedIn link below and send me your questions. Or comment on this blog. Or come to my week long class MS 6232 starting November 15!

“Seeya at tha pahty, Richtah…”

- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Ask Your Instructor: SQL Server FAQ's Part II

Got SQL Server questions? I love them! Even when I don’t have the answer right then!

I recently delivered a class on “Maintaining a Microsoft SQL Server 2008 Database” and I promised my students that I would capture their ad-hoc questions (and even some answers) in a document and, thinking that it was possible that someone else out in the web ether might have had the same question, decided to share them.

So here they are in all their glory. Enjoy!

8) Can you have multiple replication types going INTO one database?
Yes, but you must manage objname collisions manually.

9) Can you replicate from a mirror? 
Sort of… http://msdn.microsoft.com/en-us/library/ms151799.aspx  My understanding of the question is can you replicate from the database that is the CURRENT mirror. So far all of my tests have proven to me that I cannot, (but I am sure someone out there is up to the challenge). Never say it can’t be done unless you can prove it!

10) Does the restore of a database require re-initializing of a publication? 
Yes, if you want to move all copies of the data BACK to the restore point. Otherwise use synchronization to bring the restored database up to date with the subscribers.

11) Will changing the root of my IIS installation break Reporting Services?
No, IIS is handling the connection to the root of the web service so Reporting Services will simply be redirected to the new location.

12) Can you use SQL Server Express Edition as a WITNESS in a Mirror? 
Yes, you can use Express, Workgroup, Standard or Enterprise (SQL Server 2005 and later) as a WITNESS, although the Mirror and Principal can only be created on Standard and Enterprise. http://msdn.microsoft.com/en-us/library/ms175191.aspx

13) What is the heartbeat setting of a Mirror and can I change it?
The default time is 10 seconds but it can be modified using this statement on the Principal: ALTER DATABASE SET PARTNER TIMEOUT 

14) Can I run the SQL Server 2008 Profiler on SQL Server 2000? 
Yes

The questions I get from students keep these courses interesting for me and I would love to hear your questions through this blog or in my class. After all, as a long time veteran Microsoft employee told me a few years ago, “After 10 years of work on Microsoft products with a dozen certifications, you can only attain about 1% knowledge of the Microsoft technologies.”

Ok, I gotta go hit the books. My next class is probably going to ask about the other 99%... again. Stay tuned for Part II of this post!


- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Ask Your Instructor: SQL Server FAQ's Part I

Got SQL Server questions? I love them! Even when I don’t have the answer right then!

I recently delivered a class on “Maintaining a Microsoft SQL Server 2008 Database” and I promised my students that I would capture their ad-hoc questions (and even some answers) in a document and, thinking that it was possible that someone else out in the web ether might have had the same question, decided to share them.

So here they are in all their glory. Enjoy!

1) Is there any reason to use Binary Collation?
It is the fastest. More on collations at http://msdn.microsoft.com/en-us/library/ms143515(v=SQL.90).aspx

2) Can you backup and restore an entire instance?
Not in the same way as a database, no, BUT, you can right-click on the instance, choose Facets, and “Export Current State as Policy” and use this exported data with Policy Based Management to apply against another instance or even multiple instances.

3) Is sqlcmd -A (dedicated administrator connection) for the local host only? No, you can connect to a remote server using: sqlcmd –A –S ServerName

4) Where can I get a list of torn pages?
(USE msdb) SELECT * FROM suspect_pages

5) Are Windows credentials cached in a SQL Server session?
Yes, until the connection is ended

6) Where can I get a list of certificates in SQL Server?
SELECT * FROM sys.certificates

7) Is there an issue with resolving conflicting Identity columns in HTTP merge replication?
Identity columns cannot be ADDED to a publication (GUID’s are used for uniqueness) but they can be used if different values are used in the publication and each subscriber http://msdn.microsoft.com/en-us/library/ms152543.aspx

The questions I get from students keep these courses interesting for me and I would love to hear your questions through this blog or in my class. After all, as a long time veteran Microsoft employee told me a few years ago, “After 10 years of work on Microsoft products with a dozen certifications, you can only attain about 1% knowledge of the Microsoft technologies.”

Ok, I gotta go hit the books. My next class is probably going to ask about the other 99%... again. Stay tuned for Part II of this post!


- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

With an Iron Fist (SQL PBM)

Maybe you were a system admin that got sucked into the exotic land of database administration because there was no one else to do it. Maybe you are a DBA on purpose. It might even be possible that one day you were writing web applications in your remote and intentionally isolated cube at the far end of the “trailer park” and faster than you can say DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS you found yourself in charge of an unwieldy, quickly expanding and barely governed SQL Server environment.

In any case, you may have wondered, if there were some way to control the environment of SQL Server proactively and to have an automated way to enforce all of those naming convention standards and object settings that you TALKED about, documented and trained on. Have I got news for you!

In SQL Server 2008, a new feature called Policy Based Management (PBM) was introduced. The idea is that if you have a setting or convention that you wish to check for and/or ENFORCE (get your control freak on), you just create a policy that defines the object (maybe a database), a facet (the property that can be checked for or enforced, like the recovery model of a database), and a condition (the value of the property, like Full for the recovery model). Systems Administrators (yes the “A” is capitalized!) will recognize this concept as having some similarity with Group Policy in Active Directory Domain Services, although the application and verification of the policy is done quite differently.

The really fun part is that using Central Management Servers in SSMS, you can enlist one or more instances to monitor or enforce, manually or on a schedule, from one instance. You can do ad-hoc policy checks and enforce the policy on objects that are not in compliance and you can include all objects of the type within an instance or you can un-enlist individual objects.

Some examples of the types of policies you might create and assign might be controlling role membership and preventing future modification by doing a ROLLBACK when someone is added (sounds like Restricted Groups in AD), or perhaps you need to make sure that every instance in your organization must use Windows Authentication only. Maybe, instead of asking nicely and resending out that paper policy that governs object name conventions, you just need to make those ornery developers name all of their user stored procedures with a “usp_” prefix (I love you guys, really). Whatever the standardization need, PBM probably has the property you need to get a handle on. And standardizing is the whole point. It is an interesting exercise to document standards. It is a whole lot more satisfying to be able to inflict -- um – enforce them.

Hopefully, I will be speaking on this topic for SQL Saturday in Kansas City, on October 2, 2010 http://www.sqlsaturday.com/53/eventhome.aspx or you can spend some time trying it out in one of our Microsoft 6158 classes in Leawood, KS, (many of which can be attended remotely) http://www.centriq.com/CentriqSchedule.aspx.

Now go forth and standardize!


- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Joins, Joins, Joins

Hey SQL DBA's and developers, still having trouble trying to visualize joins and how they work? Well, give that part of your brain a rest and look at this article that uses Venn diagrams to make a visual representation of how joins work.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Many of my students have told me that this has helped them tremendously to get a better grasp on the concept and, admittedly, it has made my job alot easier :)

Now, certainly, there are some exceptions that cannot be expressed in this manner, and the analogy breaks down under careful examination. Please refrain from bombarding my inbox with all of the reaons that this analogy is flawed :) Most analogies are. But for those of you who are trying to get just the slightest notion of what a join does, this article is a great starting place to get your brain moving in the right direction.

And you can always come to my October class on T-SQL to have this clarified. Look forward to seeing you there!

- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Ask Your Instructor: SQL Server Permissions

Peter,

I was a student in your May 24-26 class and was hoping that you could assist me with a sequel question.

In the SQL Server there is a section to set up users, groups and permissions. If there is a change in the permissions for a group, is there any way to research when this change took place and who updated the permissions?

Thanks for your consideration.

Tricia
----------------------------------------------------------------------------

Tricia,

Yes, there is a feature called SQL Server audit and I have a link to the MSDN webpage that explains it. The basic concept is to create an audit that can have one or more audit specifications assigned to it, enable the audit and the audit specs and track any type of activity that is available through audit specs.

http://msdn.microsoft.com/en-us/library/cc280663.aspx

This should get you started. We cover this briefly in Module 4 of the 6231 class which I am teaching June 28-July 2. We don’t spend a lot of time on it but you get to see it and try it, as well as learning many other aspects of maintaining SQL Server throughout the week.

Hope to see you there. Please feel free to ask any further questions!

- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter

Cloudy with a chance of outsourcing?

If you are not yet familiar with cloud computing, the basic idea is that instead of buying your own servers and creating your own infrastructure to manage and deliver your data and services, you can hire a third party to do it for you. The provider can then add processing power and storage capacity as your needs change, theoretically reducing the headache of scaling it yourself.

Amazon was an early arrival on the cloud computing scene with the Elastic Compute Cloud service. Google Apps was launched by Google as a way to develop software online and run the apps on the monster hardware at Google.

The idea of not owning and managing IT infrastructure can be very appealing to small and medium sized businesses and can be a great solution in some cases. The main benefit is if the cost of running your business from the cloud is less expensive than doing it yourself. It usually is.

But there are many concerns about security and justifiably so. What business wants to have sensitive data out of their direct control? Which businesses can legally put that sensitive data (like maybe your patient records) out on a cloud service? Many vendors are working on ways to make the customer data more secure in the cloud, but most experts agree that it is best to keep the important data close to home inside a private IT infrastructure and only send the less sensitive data, stuff that is probably already out on a web facing server, to a cloud solution provider.

Even Microsoft has been experiencing some pushback from experts who are not employed by the software giant, as I witnessed recently at the TechEd conference in New Orleans, a venue used by Microsoft to train and promote their products. Many of the speakers at the conference spoke at great lengths about the dangers of cloud computing, security being the first concern and availability being the second. What if the cloud provider needs to bring down the network, reboot a server, change a public IP address, any of which might interrupt your service? Can your business afford to lose internet presence and for how long? How long can your business function without an application or some data? These are some of the concerns expressed by the "non-koolaid drinkers", as one speaker put it.

Personally, I think it is a great idea. Much of our data is not too sensitive to be out there and there are ways to secure data out in the public domain. Which companies truly have data that is too sensitive to allow capture by random internet hackers? Of course some companies have true, legitimate security concerns but I think most have little to fear. And as far as availability, there are many ways to have multiple copies of your data and applications available to avoid being placed at the mercy of one cloud provider by using more than one provider and I expect we will see many solutions proposed and implemented in the coming year.

I do know that Microsoft's SQL Azure, a cloud solution for databases, is very easy to set up and connect to (for a pretty reasonable fee) and that many small companies will jump at the opportunity to have a serious, professional and scalable database solution for a fraction of the cost to do it yourself. The process to set it up was described to me by one Microsoft employee:

"First you export your data to a text file or something like that, send it to us, pay the fee and we send you the connection information." When I asked him to elaborate he said "That's all there is to say..."

It just doesn't get any simpler.

Since the vast majority of businesses are small businesses, I fully expect Microsoft Azure (web services) and SQL Azure (database services) to do a fair bit of business in the next business cycle, and many will choose to cloud compute.

- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter