jump to navigation

Performance counters for performance November 18, 2009

Posted by msrviking in Performance tuning.
1 comment so far

I was reading through my blog roll today and saw an interesting blog by Buck Woody on how to use performance counters to monitor and assess performance of SQL Server system.

Here is the link and happy learning.

Cheers.

A quick update – Buck is writing a series of posts on his blog on the topic. I would suggest please visit the other parts of the series.

Advertisements

SQL Server connectivity issues November 18, 2009

Posted by msrviking in Security.
add a comment

Strangely for past few days people from different projects are approaching me to find solution for the SQL Server connectivity and login failure issues. To my surprise I see this happening all of a sudden,uh, may be the teams realized suddenly that I exist to help :). I don’t mind helping and I indeed got the issues fixed. How did I do this, well I know few of the blogs that are part of my blog rolls which gives insight information on all these kind of issues.

I thought I should share all the links which I referred as part of my finding solution. Although the links are about troubleshooting SQL Server 2005, it definitely applies to SQL Server 2008 also.

SQL Server 2005 Connectivity Issue Troubleshoot – Part I
SQL Server 2005 Connectivity Issue Troubleshoot – Part II
SQL Server 2005 Connectivity Issue Troubleshoot – Part III
Understanding “login failed” (Error 18456) error messages in SQL Server 2005

I hope these links gives you guys enough information too!

Cheers.

DB code reviews November 17, 2009

Posted by msrviking in DBA Rant.
add a comment

Sorry guys, I hadn’t been blogging for past 2 weeks. I was little tied with my work at home and office.

Well, here is what I wanted to share with you all. Yesterday one of the project managers had approached me and narrated this.

” I have bunch of developers who are not good at DB coding. Could you review the code and let us know how it looks and before we go into UAT /Production?. This will help us to perceive the probable problems we will face, and we don’t want to correct /rewrite logic in code instead we want to troubleshoot unexpected performance issues, rather”.

This kind of talk always appeases me and I gave my two cents on how we could go about this. Here is what I could tell the manager.

1. I shall review the code, share my comments on the quality of the code so that the team starts working on fixing the wrong sides right away.

2. I also want to sit with the developer and the module lead so that I share the knowledge of how the code has to be written. If we share review comments it will be only as part of statistics, or facts that code was reviewed but the knowledge of writing better code is left out. It is very essential that we “mend” the minds of the developers to write a code which looks optimal as set-based operation and try avoiding row-based operations.

To my surprise I seem to be impressing the project manager and I could sell my thoughts. I wasn’t looking for selling my thoughts, but I was interested in letting the manager know that how important is DB coding, and how we could correct in this project and in any project wherever these developers go.

Coincidentally, on similar lines if not same, Buck Woody has written blog post here which talks about “Jnan! – Knowledge” on how we could avoid post-deployment performance issues.

Enjoy reading!

Cheers.

 

A jackpot today (few intersting links) November 3, 2009

Posted by msrviking in Technical Documentation.
add a comment

Hello folks,

I was going through my blog roll today and noticed some bunch of links thats woth to look at and well I say its a jackpot on what I was doing for some time.

1. Logical Query processing – this has been great area of interest for me for quite a long time, and Itzik has done a great job consolidating all this in a flow chart. It makes life easy for people who want to understand the logical flow of the operators for a query to be processed. Thanks to Itzik and Solidq for sharing this!

2.  SQL Server Error Logs – Although I am familiar of this topic, and know the in’s and out’s of how it works but never blogged. So thought of sharing this with you guys and thanks to Jeremiah putting it so clear and simple.

I hope you guys will enjoy reading the links.

Cheers!

Timeout | Blocking | Wait types November 2, 2009

Posted by msrviking in Performance tuning.
add a comment

I am into a performance analysis and tuning assignment wherein I need to identify a “mysterious” performance issue. Sounds familiar by words, “Yes” but its really difficult if one doesn’t know where to start (the state I am in the beginning of the exercise).  Well, all these aside, but here is the problem statement given by the team “We have timeouts and deadlocks happening”.

With this small and simple one liner I started thinking like this,

– What is a time out? I had to get back to basics  for this and per definition which I love to read again and again is

“Before executing a query, SQL Server estimates how much memory it needs to run and tries to reserve this amount of memory from the buffer pool. If the reservation succeeds the query is executed immediately. If there is not enough memory readily available from the buffer pool, then the query is put into a queue with a timeout value, where the timeout value is guided by the query cost. The basic rule is: higher the estimated cost is, larger the time out value is. When the waiting time of this query exceeds the timeout value, a time out error is thrown and the query is removed from the queue.”

Well keeping in this in mind, I went hunting for memory, CPU configuration of the box.

– What is the configuration of the RAM, CPU and how is it doing?

64G RAM, and 8 core machine is having time out and deadlocks. Wow! Can’t believe it and so I decided to go to the box and check it myself on how it is doing. To my surprise all looks fine (RAM and CPU) running below the threshold levels. So what is that I am hunting for?

Over years of experience gave me a gut feeling which is usually very close to solution or at least to the  problem to start with, in such confused state of mind.  So my gut feeling was saying all looks fine so what is bad going on here.  A strong thought – maybe processes are getting blocked, and all these processes are accessing same tables through different t-sql batch of statements (procedures, functions).

– Does blocking cause timeouts?

This was the first question I had with series of questions like if timeouts are happening why isn’t evident in CPU /RAM utilization. What is going wrong and where? These questions started haunting me which led me to these interesting links which I am consuming (may be 10th time) to understand what should be my next question.

Here are the links for you guys

http://www.informit.com/articles/article.aspx?p=686168

http://sqlserverpedia.com/wiki/Wait_Types

I am curently using these links to arrive at some information for analysis and tune the damn queries causing performance issue through blocking.

I shall keep you guys posted on how I progress, but remember yes blocking & wait type are related (although I didn’t mention about wait types explicitly in the post) and yes of course you have time outs because of blocking which I shall confirm it after this.

Maybe you guys know all these, and even I do but thought of blogging as my thoughts were flowing else I need to spend “regurgetating” you see ;).