Microsoft made available the Troubleshooting Performance Problems in SQL Server 2008 white paper on their site. This white paper is 102 pages long, the scope of this white paper is limited to the problems commonly seen by Microsoft Customer Service and Support (CSS). Below is what is covered in this white paper: _Introduction Goals Methodology Resource Bottlenecks Tools for Resolving Resource Bottlenecks CPU Bottlenecks Memory Bottlenecks I/O Bottlenecks tempdb Slow-Running Queries

As promised from topic http://forum.lessthandot.com/viewtopic.php?f=17&t=4735&start=0&st=0&sk=t&sd=a&hilit=DBA I’m making a blog entry on my expectations of a DBA. My reply to the question DBA in my eyes and how I perform the job is… Mid to Sr level TSQL abilities in order to fulfill business requirements in data access not directly available to developers. Personally don’t think a DBA is much if they don’t have at least mid-career skills in TSQL. They should be the ones telling the developers what not to write and how to write it. It will hurt them if they can’t. Proactive monitoring of all resources including any instances currently installed in the environment. This includes development and test. Be able to fix them!!! Proactive monitoring of performance bottlenecks. Blocking, deadlocks, memory pressure, load balancing due to pressure, index, statistics, usage of these. Load balancing references things like pushing reporting off main databases to prevent issues etc.. Create a stable security landscape. Schemas, user roles etc… and maintain it! If someone knows sa then they failed. Knowledge and ability to install and configure “PROPERLY” any SQL Server service. SSRS, SSIS, SSAS etc.. If you are titled SQL Server DBA then you better know it’s not just a DB Service and know how to maintain and configure them all. Ability to communicate high level DB Server issues to peers in less technical manner. Can you explain a deadlock and actually have a manager understand it? Important! You won’t get hardware upgrades without it. Document everything. Landscape, security, service configurations Ability to setup and configure a stable and trustworthy disaster recovery model. Achieve at least a 98% up time goal during open and critical business transactions. Finally, I think any DBA should show what they do in no less than a monthly report to upper management. I say upper sense typically this is where they report to. Monthly I send the directors a comprehensive report of performance of the production system. This including known issues and tasks open and or closed. Also contains project lists. The reason for this is one to show the business your worth and two, in order to maintain your db server landscape and have the ability to request upgrades and or scaling out you need to show and sell it. These reports can be critical to that IMO. If they can’t provide you with the current state of the database servers then they don’t know it and there is a point of failure.

Did you know you can add layout styles to the report wizard and load into existing reports? I’m referring to the list that contains slate, forest, generic etc.. I always thought this was pretty cool and thought I would share how to do it. First go into your respected Visual Studio installation directory. I believe the location is the same and has not changed from 7,8 and 9. Find this directory

I just had the pleasure of going through this again on my machine so I figured I would put it up there for people that have yet to gain the experience of a complete SQL Server failure from system database corruption (suspect), disk failure and a few others. I’m going to break my favorite installation of SQL Server 2005 so you can see how to recover it. First to show the status of master we run

For reports that requires start and end dates as parameters, typically the default dates are set as today and today + 1 day or today + 1 month. Instead it might be a good idea to pick up the start and end dates from the database. This way the user will not have to play the guessing game of trying different date ranges to generate a valid report. To do this, create a new dataset. Paste the below query

I am reading this very good book. It’s called Head first Design Patterns by Eric Freeman & Elisabeth Freeman. It covers all the basic design patterns an OO-developer could ever use. Actualy, if you are doing OO programming you might see that you were already using some of these patterns but didn’t know it. Alle the patterns are quite well explained in a simple matter. And we all know what Einstein said about explaining things simple. But for those of you who didn’t.

With the type of databases that can be set to nightly or even weekly recovery points, the copy database task is ideal and a quick way to setup you DR services. Many times the copy database task is overlooked for tasks such as disaster/recovery and test or development resources. This task available is very useful for databases that primarily hold meta data or are relatively small in size. The company I am with now has 3 primary third party installations that have databases in size ranging from 500MB to only 2GB. These are meta data and logging databases installed with the third party applications. It’s almost not worth the resources and monitoring efforts to have these databases setup for log shipping, mirroring etc.. for DR purposes. The databases only consist of configurations for the software and key recovery points rarely change.

Time for another episode of the SQL Friday, The Best SQL Server Links Of The Past Week show, the Good Friday edition. Here is what I found interesting this past week in SQL Land: Updated Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems The PSS SQL Server Engineers explain what causes this, here is just a part of the post

Managers and users love Excel. We cannot get around it, no matter how horrid it is to load and work with Excel from a systems stand point. In the goals of automating our lives so we can be lazy DBA’s, you’re going to want to look into Database Mail to do this type of task. Why not? Database Mail is one of the best upgraded features of SQL Server 2005+. Remember what we went through to do something as simplistic as sending an email prior? Data gods help us if we had to attach a file. Oh man it was hell! Database Mail is awesome and having the @query_…. parameters is even more awesome, but it’s not the quickest and best way to do this task. I don’t find it to be that great unless my data is so dynamic I have little choice. Remember that reporting platform they were so kind to give us called Reporting Services? Yeah, if it’s not up and running somewhere then get it running. SSRS is more than just a point, click, run my report, make VP happy and out of my hair. You have email capabilities with SSRS by creating subscriptions. The best part is you have options to manipulate report results into a ton of formats. By default you should see csv, web, pdf, excel and a few others. You can even add more by configuring SSRS in depth. So why be the “Cool DBA or Developer” and script all that junk out? I like being lazy. I get more done 😉

Most often early in a DBA’s career they will go off on a job creation spree. This is actually good. I’ve always gone with the feeling that nothing cannot be automated. The agent gives us that freedom of automation and notification on tasks. Schedules can be robust, most tasks can be run and you can strategically manage tasks to prevent them from tripping over each other. The problem that you may run into is a poorly configured SQL Server Agent. More or less you simply set the agent to start up upon data services start up and you forget about it. CPU idle times, time out settings and history management are ignored.