Friday, August 12, 2011

CPU performance testing using SQL Server : a test plan



                                 CPU performance testing using SQL Server : a test plan
                                                          Saptarshi Biswas
Apart from SQL Server we need to download and install the following free softwares:
I.                    SQLIO
II.                  SQLProfiler
SQLIO: This is a tool from Microsoft that could be used to test the disk performance with respect to load generated by batch files created by SQLIO.
As customer is not interested in testing the storage performance so we could use this tool in combination with SQL Profiler .
SQLProfiler, with other capabilities, it can monitor and log the CPU usage.

Perf Mon could also be used for the same purpose.
1.       Install the SQL Server binaries on local disk
2.       Create a database ( preferably with command, see my example below) with
File#1 and File #2 on two separate drives.
E,g,
D Drive=SQL Data
Size: however large your database will be)
E Drive=SQL Logs
Size: typically 20% the size of the SQL Data volume.
3.       Install SQLIO
4.       (a) After installing SQLIO, edit the param.txt file (it comes with the installation)and change these two parameters:
First parameter - the physical location of the testing file. Change the drive letter to point to the SAN drive you want to test, like T:\testfile.dat.
Last parameter - the size of the testing file in megabytes. Increase this to 20480 or larger. Ideally, you want it to be larger than your SAN's cache, because your real databases will be larger than the SAN's cache.
After saving param.txt, run this at the command line in the same directory where SQLIO is installed in order to create the test file:
sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10

(b) When it finishes, your test file is created, and it's time to run our real tests.
5.  In parallel use the SQL Profiler

(a) In SQL Profiler, on the File menu, click New, and then click Trace.
   2. Specify the relevant connection details to connect to the computer running SQL Server.

      The Trace Properties dialog box appears.
   3. In the Template name list, select SQLProfilerTSQL_Duration.
   4. Select the Save to file check box, specify the trace file name as c:\MyTrace.trc, and then click Save.
   (b) On the Data Columns tab, select the following data columns, clicking Add after each selection:
          * CPU
          * IntegerData
          * Reads
          * StartTime
          * Writes
   (c )  Click Run.
   (d)  In SQL Query Analyzer, run the queries you want to analyze. For example, using the Pubs database, you might run the following query.

      select au_id, au_lname + ' ' + au_fname, phone from authors where au_lname like 'G%' order by au_lname
      select * from authors
      Select au_id from authors

   (e) Stop the trace.
   (f) In SQL Query Analyzer, import the trace file into the database of your choice by using the following query:

      SELECT * INTO MyTraceTable FROM ::fn_trace_gettable('C:\MyTrace.trc', default)

Combining SQLIO and the SQL query executed via SQL Analyzer the database will be stressed and the trace file will show the result.

1.       Sample db creation script: 
       CREATE DATABASE [SaptDB1] ON  PRIMARY
( NAME = N'SaptDB1', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SaptDB1.mdf' , SIZE = 1024000KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'SaptDB1_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SaptDB1_log.ldf' , SIZE = 512000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [SaptDB1] ADD FILEGROUP [Extra1]
GO
ALTER DATABASE [SaptDB1] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [SaptDB1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SaptDB1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SaptDB1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SaptDB1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SaptDB1] SET ARITHABORT OFF
GO
ALTER DATABASE [SaptDB1] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SaptDB1] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SaptDB1] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SaptDB1] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SaptDB1] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SaptDB1] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [SaptDB1] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SaptDB1] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SaptDB1] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SaptDB1] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SaptDB1] SET  DISABLE_BROKER
GO
ALTER DATABASE [SaptDB1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SaptDB1] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SaptDB1] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SaptDB1] SET  READ_WRITE
GO
ALTER DATABASE [SaptDB1] SET RECOVERY FULL
GO
ALTER DATABASE [SaptDB1] SET  MULTI_USER
GO
ALTER DATABASE [SaptDB1] SET PAGE_VERIFY CHECKSUM 
GO





No comments:

Post a Comment