SQL Server – simple query performance analysis

I’m still reading for my next exam stuff for 70-461. During the chapter “Using Tools to Analyze Query Performance” I found a quite simple way of measuring the query performance by using the SET STATISTICS command.

Example:

DBCC DROPCLEANBUFFERS; 
SET STATISTICS TIME ON; 
SET STATISTICS IO ON; 
SELECT * FROM Sales.Customers; 
SELECT * FROM Sales.Orders;

Note that DBCC DROPCLEANBUFFERS clears data from the cache. SQL Server caches data besides query and procedure plans. The TIME switch will display how many miliseconds the query took to execute, while the IO switch will tell how many logical and physical page reads that was involved.

A page in SQL Server is a physical unit on a disk. The size of a page is fixed to 8,192 bytes. A page belongs to a single database object, such as table, index or indexed view. Pages are further grouped into logical groups of eight pages called extents. An extent can be mixed, if pages on this extent belong to multiple objects.

 Output from “SELECT * FROM Sales.Customers” command:

(91 row(s) affected)

Table 'Customers'. Scan count 1, logical reads 5, physical reads 1,   
read-ahead reads 3,   
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:    CPU time = 15 ms,  elapsed time = 125 ms.

Output from “SELECT * FROM Sales.Orders” command:

(830 row(s) affected)

Table 'Orders'. Scan count 1, logical reads 21, physical reads 1,   
read-ahead reads 19,   
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 330 ms.

The meaning of the information returned is as follows (from Traning Kit for 70-461):

• Scan count The number of index or table scans performed.
• Logical reads The number of pages read from the data cache. When you read a whole table as in the queries from the example, this number gives you an estimate about table size.
• Physical reads The number of pages read from the disk. This number is lower than the actual number of pages because many pages are cached.
• Read-ahead reads The number of pages SQL Server reads ahead.
• Lob logical reads The number of large object (LOB) pages read from the data cache. LOBs are columns of types VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, IMAGE, XML, or large CLR data types,  including the system CLR spatial types GEOMETRY and GEOGRAPHY.
• Lob physical reads The number of large object-type pages read from disk.
• Lob read-ahead reads The number of large object pages SQL Server reads ahead.

SQL 2012: WAITFOR DELAY/TIME

Today is discovered a command in T-SQL I never have used before: WAITFOR

This is not the most important command But nice to if you need to wait for a predefined number of seconds or until a certain time of day in your scripts.

Examples:

• WAITFOR DELAY ’00:00:20′;
  pauses code execution for 20 seconds
• WAITFOR TIME ’23:46:00′;
  pauses execution to wait for a specific time

This command can be used to syncronize processes in a simple fashion from your scripts. These scripts often run from command line, scheduled tasks or similar applications.

SQL Server – Max Server Memory

There have been a lot of performance tuning and debugging lately on SQL Server, and one recurrent factor is the lack of configuration regarding the SQL server setting ‘Max Server Memory’.  This setting tells SQL Server how much memory it can use. The rest of the memory is reserved for operating system and other applications.

There is no perfect “rule of thumb” for this value, but a reasonable value can be set using the calculation for how much OS memory that should be allocated:

1. Reserve 2GB for the OS
2. Reserve 1GB for each 4GB of RAM installed  from 4-16GB
3. Reserve 1 GB for every 8GB installed above 16GB RAM

Based on this, the following examples can be used

• Server memory 8gb => 4gb OS, 4gb SQL (Max Server Memory)
• Server memory 16gb => 5gb OS, 11gb SQL
• Server memory 32gb => 7gb OS, 25gb SQL
• Server memory 64gb => 11gb OS, 53gb SQL

A simpler “rule of thumb” is to set “Max Server Memory” to a number between 70-80% of total server memory. This is not an exact science, and the main rule should be not to give SQL Server all available memory on the server, because SQL Server is greedy

SQL Server 2012 – installing and configuration – part 2

In my previous post, I gave some overview thoughs on how to install and configure SQL Server 2012. These recommondations are collected in my work in current project and SQL Server 2012 certification. I have got me subscription at Pluralsight where they lots of training available. In addition, I pick up recommendations on MSDN SQL Server forums, SQLBlog.com, MSSQLTips.com, SQL Server Team Blog, SQLTeam.com and other resources.

Pre-install tasks

Service accounts

Aquire service users. These users should a normal domain user, and not local administrator, and the password should be set to “Never Expire”. Here is a suggestion for naming convension for the SQL Service accounts. You might not need all the accounts. Only install the components that you need to increase the server performance.

Here is standard naming scheme I have used several times.

• Database (Engine) Service = Svc-SqlDB-xxx
• Agent Service = Svc-SqlAG-xxx
• Reporting Service = Svc-SqlRS-xxx
• Analysis Service = Svc-SqlAS-xxx
• Integration Service = Svc-SqlIS-xxx

Physical vs Virtual

The last few months, there have been an discussion (and still is) if we are able to install SQL server in a virtual environment rather than physical servers. EVRYs new concept “Future Proof” is based on high performance data center technology and we wants most of our servers to be virtualized. This will enable us to fast transitions between environments. With today’s technology, this is possible to, depending on the customer SLA requirements. Our main question for our decision is how the PCI-E storage cards perform in a SQL Cluster.

Storage type and space

You have many options when it comes to selecting storage type. Here are the four main types:

• Internal drives – traditional magnetic drives or solid state drives (SSDs)
• PCI-E Storage Cards
• Direct-attached storage (DAS) – traditional magnetic drives or SSDs
• Storage area networks (SAN) – traditional magnetic drives or SSDs

What kind of storage you shall use also depends of the main usage of the SQL server. A standard multi-usage SQL Server should have enough disk space, memory and CPU power.

• Online Transaction Processing (OLTP). Frequent writes to data files and log file make random I/O performance very important
• Relational Data Warehouse (DWH).  Large sequential reads from data files makes sequential I/O performance very important
• Online Analytical Processing (OLAP). Lots of random reads from cube files makes Random I/O performance very important.

When requesting storage type for these types of databases, the sequential performance is measured in MB/second or GB/second, and random performance is measured in input/output operations per second (IOPS).

CrystalDiskInfo is a good tool for measuring disk performance recommended from different sources. Another Microsoft tool for benchmarking disk system is SQLIO.

The RAID level should also be considered regarding redundancy and workload. RAID 5 is often used as a cost-effective level for backup while RAID 10 is most popular among DBA due to very good write performance.

Disks and directories

When working for customers, it is always annoying to when their servers don’t have a standardized naming scheme for disks and directories. This often make people (us consultants) wonder where things are located and we use much more time to pinpoint problems if this is our main task for the assignment. This applies specially if SQL clustering (Always-On) and/or mirroring is used for the SQL Servers. Jeremy Kadlec has good post on recommended disks and partitions.

As a general recommendation, data, log, tempdb and backups should be separated into different disks. In my current project, we use the following disk partitioning:

• DTC: H-drive
• Data: I-drive
• Log: J-drive
• Tempdb: K-drive
• Backup: M-drive

The drive letters it not important. It is the separation in different LUNs that are important.

The directories are not that important either, I normally hates “Clicking Hell” with deep directory structures, so I recommend the following

• I:\SQLData
• J:\SQLLog
• K:\SQLTempdb
• M:\SQLBackup

Windows Update, Service packs, drivers

Of of the first things you should do when Windows Server is up and running is to disable automatic installation for Windows Update. You will not risk outage for your SQL Server during daytime or when backup performed. Windows Update should be configured to automatic download, BUT with a notification only. Remember to have the latest service packs for Windows Server.

Depending on your company policies, you should always make sure to have the latest BIOS and other firmware. In addition, you should also update all device drivers, such as RAID controllers, Host Bus Adapters (HBAs), PCI-E storage cards and proper Network Interface cards (NICs) and the main board chip set driver packages.

Power Management

If there is a power management option in BIOS, this should be set to OS controlled. If this is not possible, this BIOS setting should be disabled. The power management is by default set to “Balanced” power plan in Windows Server. For a SQL server, this can decrease the performance a lot. Therefore, you should always set the power plan to “High Performance”. You can verify the CPU speed and how it behaves in the Task Manager, or use the nice utility cpuid.com

SQL Service Account Policy Rights

There are two policy right that should be added to the SQL servcie account. The first “Perform volume maintenance tasks” enable Instant file initialization, reduce time for data file creation and speed up growth time. The second setting “Lock pages in memory” prevents OS from trimming the SQL Server working sets, but this is only available in Enterprise edition.

Max Server Memory

One of the important settings is the Max Server Memory available from instance properties in SSMS or from sp_configure. SQL Server will acquire as much as it can for its operations without regards to other applications and drivers. I have a golden rule to never let SQL Server 2008/2012 get more than 70-80% of the available memory on the server, depending on what services running and what SQL server components that is installed. Jonathan Kehayias has i nice post on this setting.

Other considerations

• Make sure your server has a static IP address
• SQL server should be added to the domain
• Configure anti-virus to skip .MDF, .NDF, and .LDF files
• Make sure there are no pending reboots in Windows

SQL Server 2012 – installing and configuration – part 1

The few past weeks I have done a lot planning for the SQL hotel, and discovered lack of knowledge in this area. Everybody can put a DVD in the player, and start the setup program and select most of the default provided by installation wizard - it works. But when an enterprise SQL solution shall be established, you need to make a lot of choices and preparations before you can start the installation.

My current project is special because I have been doing stuff that normally is delegated to IT-department. My employer EVRY have been in a transition and the new concept “Future Proof” shall be the new delivery template for a secure, automatic, mobile and stable platform for our customers.

A customer delivery and will in most cases consist of a shared or dedicated development, test, QA and production environments. Each of these environments will contain a set of application. I’m part of the SQL Server team that plan, design and install the SQL server infrastructure used by Citrix, Lync, Exchange, CRM and DWH, in addition to other customer systems.

I will divide this subject into 4 different parts, hopefully completed in a few weeks

1. Pre-install OS preparations (Part 2)
2. Pre-install SQL preparations (Part 2)
3. Actual SQL Server installation (Part 3)
4. Post-install configurations (Part 3)
5. Maintenance (Part 4)

The first thing you need to consider is to what licenses the customer currently have available. In most cases we need SQL Server 2012 “Enterprise” edition in QA and PROD environments, but there exist a “Business Intelligence” edition as well, while the TEST/DEV environments can use ”Standard” edition.

When you install SQL Server, many people might install the full package with all feature enabled. This is not neccesary. Only install the components that is acually needed. This will reduce the resource usage and complexity of the installation, configuration and maintenance.

Windows 8: shutdown/restart tile

Wow…the Script Center was a great site. Loads of nice script for whatever you need in a Microsoft environment. One script that caught my eye was a powershell script for creating showdown and restart tiles in Windows 8.

I first got the error message: “cannot be loaded because running scripts is disabled on this system“

This was resolved by the following powershell command: set-executionpolicy remotesigned

After this I ran the following commands

• New-OSCWindowsTile -ShutdownTile
• New-OSCWindowsTile -RestartTile

And finally, I moved the tiles at the beginning of the “Start Menu”….Nice

 

SQL Server: db space status

A collegue of mine came a cross a nicly formatted script technet ”All Databases Data & log file size, space used & free space” today. After testing it I replaced instantly with my current one

Here is the result after running it on my local laptop database.