jump to navigation

Links on high availability November 27, 2008

Posted by msrviking in Technical Documentation.
1 comment so far

Bucky Woody in his blog has a post on some useful links on DB Mirroring and other high availability features of SQL Server 2008.

Please utilize these resources for more information on high availability features of SQL Server.

Advertisements

SQL Server 2008 at its best November 26, 2008

Posted by msrviking in SQL Server 2008.
add a comment

Guys, I was reading through my favorite blog rolls and I happen to get into this link. Very interesting stuff on how SQL Server 2008 is performing with billions of TSQL statements. I am amazed with this guy called “SQL Server”. Kudos to SQL Server team for bringing us until here! I remember how I used to struggle to fix things in SQL Server 6.5 10 years ago and today, well, I don’t have anything to talk about because the product talks for itself.

Enjoy!

SQL Server 2008 Upgrade technical reference November 26, 2008

Posted by msrviking in Technical Documentation.
add a comment

It’s good to see that MSFT has released much awaited (at least by me) SQL Server 2008 upgrade technical reference guide. Check this link to download a 7M file consisting of 450+ pages of full blown content (hmm!).

All the best, folks!

Microsoft Whitepapers on SQL Server November 25, 2008

Posted by msrviking in Technical Documentation.
add a comment

This link is probably known, but I thought to blog this so that any body could reference.

There are bunch of whitepapers at this url, worthwhile for your reading and assimilating knowledge of SQL Server and related stuff.

Enjoy!

PASS 2008 – Important annoucements November 20, 2008

Posted by msrviking in General.
add a comment


Although I can’t be a participant in PASS 2008 (A group for SQL Server Professionals), but I am keeping track of what is happening during the summit which started on Nov 18. Here is something which could interest you all. Thanks to Aaron for writing the points and indeed he made me feel as if I was at the venue.

I wish myself for better luck next time so that I could attend the summit.

SQL Server Virutalization November 17, 2008

Posted by msrviking in SQL Server 2008.
add a comment

I have been fascinated with the concept of SQL Server virtualization for quite sometime. All the while, I had the belief that virtualization is for consolidation of sprawling SQL Servers in different environments. Although, it (virtualization) does help to consolidate, but there are other benefits for doing this. I happen to visit this website Virutalization, by MS team and this site gives information on How to Virtualize, How best you could utilize your environments  hardware resources, and many others.

My advise would to read the stuff in this website and empower yourself with latest happening of IT – Virtualization.

Happy reading!

AS 2008 Performance Guide November 17, 2008

Posted by msrviking in Technical Documentation.
add a comment

Guys,

AS 2008 Performance Guide is out and you could download your copy from here SQL Server 2008 White Paper: Analysis Services Performance Guide

Cheers!

SQL Server 2005 I/O issues – Part 2 November 4, 2008

Posted by msrviking in Performance tuning.
2 comments

As a continuation of my earlier post SQL Server 2005 I/O issues – Part 1 here is what had happened after my initial analysis. With more indicators from the system administrators, my apprehensions started coming true – that there is a disk IO issue in the system.

To satisfy my curiosity and to strengthen my apprehensions into facts, I went ahead and configured performance counters,

PhysicalDisk Object:

1. Avg. Disk Queue Length

2. Avg. Disk Sec/Read

3. Avg. Disk Sec/Write

4. %Disk Time

5. Avg. Disk Reads/Sec

6. Avg. Disk Writes/Sec

Database:

1. Log Bytes Flushed/sec

2. Log Flush Waits/sec

And few others like User connections and Processor time for a 24 hour period spanning the off-peak and peak hours of the application usage, the counters being polled every 30 seconds.

After gathering the results from the .csv log, I picked the data for key counters Avg Disk Read (ms), Avg Disk Write (ms), % Disk Time, Log Bytes Flushed/sec, Log Flush Waits/sec for the period of 8 AM – 5.30 PM knowing that the application would have got the hardest hit during these hours.

Now I have the data ready, I decided to bring up some graphs so that my analysis becomes easier, and conclusions will be quicker. Here is what the analysis (using DMV’s and graphs) talks about

<Start of Report>

DMV results:

Percentage of I/O for each database

Database Name

IO in MB

% age

Tempdb

109986

63.79

NGDemo

51205

29.7

Msdb

2018

1.17

Master

31

0.02

Model

8

0

Database and IO Stall

Database Name

IO in MB

io_stall_s

io_stall_pct

tempdb

109986.17

40046.95

50.94

NGDemo

51205.22

19398.37

24.68

msdb

2018.13

7711.44

9.81

master

31.86

52.67

0.07

model

8.05

2.61

0

Although there are no threshold values defined for any system for the counters % age, io_stall_s or io_stall_pct but any high numbers are the pointers for us to investigate. Here io_stall means “Total time, in milliseconds, waiting for the IO to be completed.” and for purpose of calculations and reports the io_stall value is converted into seconds.

There is benchmark number defined for the above counters and here is the quick gist of these,

Avg. Disk Sec/Read and Avg. Disk Sec/Write: The average time, in seconds, of a read of data from the disk. Use the following to analyze numbers in the output.

·         Less than 10 milliseconds (ms) = very good

·         Between 10-20 ms = okay

·         Between 20-50 ms = slow, needs attention

·         Greater than 50 ms = serious IO bottleneck

Physical Disk: %Disk Time: The percentage of elapsed time that the selected disk drive was busy servicing read or writes requests. A general guideline is that if this value > 50 percent, there is an I/O bottleneck.

Database: Log Bytes Flushed/sec: The total number of log bytes flushed. A large value indicates heavy log activity in tempdb.

Database:Log Flush Waits/sec: The number of commits that are waiting on log flush. Although transactions do not wait for the log to be flushed in tempdb, a high number in this performance counter indicates and I/O bottleneck in the disk(s) associated with the log.

The graphs talk about what is happening in the disk of the system and tempdb, which lead me to conclusions and action points.

Graph 1: For better performance, the values of Avg Disk Read time in ms should be less than 20. But the results show that we have average read time between 20 -50 ms and sometimes above 50 ms which means that we need to pay attention to IO and tempdb performance.

Avg Disk Read Time (ms)

Avg Disk Read Time (ms)

Graph 2: For better performance, the values of Avg Disk Write time in ms should be less than 20. But the results show that we have average read time between 20 -50 ms and sometimes above 50 ms which means that we need to pay attention to IO and tempdb performance.

Avg Disk Write Time (ms)

Avg Disk Write Time (ms)

Graph 3: For better performance the % Disk time should be less than 50. The below results show that we have touched /crossed 50, only 2% of the duration which isn’t alarming.

% Disk Time

% Disk Time

Graph 4: For better performance the Log Flush Waits/Sec should be always as low as possible, and per below results we are not having issues with transaction log of tempdb. Moreover, the Log Bytes Flushed/Sec shows high tempdb log activity with bottleneck at transaction log of tempdb.

Tempdb log file activity

Tempdb log file activity

With the above findings, and conclusions I could probably take the below approach for fixing the performance issues from tempdb.

1) Configure the tempdb per best practices and recommendations – Move the tempdb into a drive with RAID level (RAID – 10) of higher disk performance and if possible on to different LUN, configure tempdb with more data files and appropriate growth parameters

2) Observe the performance of the tempdb after the changes

3) Monitor and identify the performance bottlenecks of SAN. In case of any performance issues with SAN configuration, we fix the issues and monitor again

4) If necessary run IO meter on the system to test the performance of the disk and SQL Server, analyze the results and implement changes followed by monitoring

What do you guys think about all the analysis and conclusions? Do let me know.

UPDT: I have been seeing for quite sometime I had posted this entry, that hits on this article have been really high. So I thought I should conclude on the findings and what was done on the systems based on the recommendations.  Well finally the performance of the tempdb got better after having TempDB moved to RAID-10 and the problem was solved. I hope all this helps you guys!

Enjoy until next posting, cheers!

SQL Server 2005 I/O issues – Part 1 November 3, 2008

Posted by msrviking in Performance tuning.
2 comments

I had this issue presented to me by one of my customers. Alright what is the issue? – “During peak hours of the day the performance of the application slows down.” With this single statement and few other pointers on the application environment I was requested to hunt down the problem from a database perspective.

As I started looking into what could be the cause of performance issues “if any” on the database server, I was getting multiple ideas, but was unable to pin-point what could be the cause, where and what to look for.

So I requested my system administrators to help me in finding what is alarming – CPU, Memory or Disk subsystem on the box. If I know where the cause through the performance counter is, I will know what kind of queries should be run against the instance DMV’s. My request was probably the toughest with no indicators around still, I went and took the armory I use for finding these type issues. Before I used the armory, I categorized the problematic areas as

  • CPU
  • Memory
  • Disk Subsystem

I ran around 7 out of 13 emergency diagnostic queries (some of these queries are from Glenn Berry’s list, some from the Troubleshooting Performance in SQL Server 2005, and some from Working with tempdb in SQL Server 2005) and here is what are all these about, what did yield and what could we do further.

My other thoughts were to wait for reports from Windows front to conclude further on what needs to be done. I was also thinking to have the performance counters for CPU, Disk Reads, Writes, RAM to be configured for logging so that we could arrive at better conclusions.

My findings and inferences were as below

— HIGH CPU *******

— Isolate top waits for server instance

What are the results?

There is one wait type by SQLTRACE_BUFFER_FLUSH, wait time is 2037408, and running percentage 97.45.

What do we infer?

Occurs when the SQL Trace flush task pauses between flushes. This wait is expected and long waits do not indicate a problem.

What do we do?

One thing that is making me wonder is why do we see this kind of wait type. I haven’t thoroughly analyzed on this part, and I shall get back with more inputs in case if it’s going to be worth enough.

— HIGH CPU *******

— Check SQL Server Schedulers to see if they are waiting on CPU (runnable tasks above 10 is very bad)

What are the results?

None of the cpus were busy when I ran this query. The values of runnable tasks were zero.

What do we infer?

The run of this script was not at the right hour, and the information gathered at off peak hours (AM hours) wasn’t judgeable.


What do we do?

We should probably run the script during the peak hours for ½ to find if any of the cpu cores is facing issues.

— TRANSACTION LOG FULL *****

— Find the log reuse description for the transaction log

What are the results?

None of the transaction logs of the user and tempdb were waiting for any operations to complete, when I ran this query. The values of run able tasks were NOTHING.

What do we infer?

The run of this script was not at the right hour, and the information gathered at off peak hours (AM hours) wasn’t judgeable.

What do we do?

We should probably run the script during the peak hours for ½ to find if any there are performance issue faced by transaction logs to write the data.

— IO ISSUES *****************

— Analyze DB IO, ranked by IO Stall %

This query returned the most interesting results.

What are the results?

The query returned parameters like which database and which file of the database is having issues. The most prominent database that had high stall value and percentage was tempdb, and the log file being the most victimized.

File type io_mb io_stall_s io_stall_pct

log 12063.21 14488.00 23.76

data 69810.41 13228.62 21.69

What do we infer?

The query returns a value called io_stall which means “Total time, in milliseconds, that users waited for I/O to be completed on the file”. The value we get in the above results is in seconds. I have the feeling that tempdb log file is facing issues while writing data.

This kind of stalling would happen when the configuration of the log file is not optimal to perform the kind of operations generated by the application calls or the disk subsystem is tiring up doing the operations of tempdb log file.

What do we do?

My advice was to monitor tempdb utilization, and monitor the disk subsystem (drive counters) where the tempdb is configured for sometime before we make any changes. We don’t have any threshold values defined to say if the above numbers are concerning are not, but further monitoring would help to deduct further.

As I was writing this post, I thought I should keep this as Scene – I of the Act – I and will shortly post on Scene – II of the Act – I.

Until then enjoy reading, and let me know if you have other ways to find out such issues.

SQL Server 2008 is touching new heights November 3, 2008

Posted by msrviking in SQL Server 2008.
add a comment

Read this for yourself, and feel the new heights of SQL Server 2008 on what it does with Siebel CRM.