Skip to main content

DBA 101 - Troubleshooting a slow server

User groups, special classes, informal meetings, etc and this topic comes up more often than not. What is really surprising is the tentative answers that are given. Simply stated people want to know how to troubleshoot a problem. Isn't there a basic set of steps, bullet points, something to help me troubleshoot any kind of problem on my server? Instead of beating around the bush and saying "it depends on the problem" I will attempt to tackle this head on.

New DBA's or accidental DBA's may not have all the knowledge that is needed to identify and fix a problem. However, the knowledge is simple enough to explain and fumble through your first few times.

What you need to know:
  • Activity Monitor
  • DMV's (Dynamic Management Views)
  • Query Execution Plan
  • T-Sql knowledge
  • Performance Monitor (a.k.a. PerfMon)
  • Windows event viewer

If you don't know these things not to worry. We will go over them, what they do, and how you should use them. Look for more on this soon!

Comments

Post a Comment

Popular posts from this blog

Why fragmentation occurs and how to avoid/fix it.

Let's suppose you have this table: We have made last name the primary key in this table Now lets import some rows into the table and check the fragmentation Notice the fragmentation at 96.48% and all we did was one import of 16426 records. So now we have several questions: How bad is this? Fragmentation causes SQL Server to skip around to read data from your tables. For a one time read on a small table this is virtually meaningless. In a job that may need to read millions of records, this could tear apart your performance. Ok this is bad. How did it happen? The primary key on a table is clustered. This means the data will be stored according to the primary key. In our case, last name. When data doesn't come in the same way you store it (i.e. Customer names are not coming in alphabetically, but rather randomly) SQL Server must constantly split pages to store the data correctly. This causes data to be "fragmented" into multiple areas of the disk instead of one continuou...
Post a Comment
Read more

SQL Server ETL for Data Lineage

What is data lineage? Ok let us suppose you built a wonderful database with loads of data coming from source files from your vendors, your own AS400, and different departments of your own organization. All of this data has been imported into 200 tables in this database producing a plethera of information that is used for reporting purposes. One day a department head comes to you and says, "I think this number here on this report is wrong. Where did you get it from?" Do you escort that person to the computer room and show him/her your server? No, I didn't think so. So how do you come up with where the information came from? Likely you find a data load expert in your IT department and have him/her spend the next several minutes/hours/days rummaging through stored procedures, ssis packages, dts packages, custom applications, etc trying to find this information. However, if you had this: you could simply pull up the history of how that data came to be loaded and point. How us...
Post a Comment
Read more

Why embedded sql should not be in your applications

So very recently our team had a lunch meeting presentation as we sometimes do where a person will give a presentation on a topic. This day Charlie gave a presentation on LINQ to entities. You can see the camtasia video here . LINQ stands for Language Integrated Query. LINQ is very powerful and I like many things it offers. After meeting discussion turned to why developers write their own queries inside their applications (a.k.a. embedded sql). Developers and DBA's will clash over why you should/should not put sql in the application. The developers point of view: I don't need a DBA or SQL Developer to complete my application. I just need a connection to the database and I'm golden. Writing stored procedures and waiting for the DBA slows me down. I have deadlines! Understanding the program is much easier if all the code is in one place and not spread to the sql server. The DBA's point of view: SQL in the application requires the connection to have direct access to every t...
Post a Comment
Read more