SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: August 2011

Mobile Commerce and SQL Performance Tuning

I was reading through Gartners Top Predictions and one prediction captured my attention:

 “By 2014, over 3 billion of the world's adult population will be able to transact electronically via mobile or Internet technology”

 The reasons given are:

 1)   “6.5 billion mobile connections by 2014”

2)   “Global adult population to… Read more

0 comments, 1,538 reads

Posted in SQLServer-DBA on 31 August 2011

TRUNCATE_ONLY is not a recognized BACKUP option

Executing the code below in SQL Server 2008 will throw an error message:



Msg 155, Level 15, State 1, Line 44
'TRUNCATE_ONLY' is not a recognized BACKUP option.

It was deprecated in SQL Server 2008.

 Some options for SQL Server 2008 are :

  Read more

0 comments, 6,753 reads

Posted in SQLServer-DBA on 30 August 2011

Datawarehouse Trends – In-memory

What does the emergence and growth of In-memory databases mean for  Datawarehouse? 

 1)  In-Memory database server systems are  fast.  Consideration should be given to hardware purchases, configuration and consolidation. I’ve been working with solidDB over the last few months and the speed differences are obvious. The Query response and commit times are… Read more

0 comments, 1,545 reads

Posted in SQLServer-DBA on 30 August 2011

Storage Checklist FAQ

In a new data centre build, storage system or new equipment, the DBA should have an input into the architecture and configuration. 

This is an FAQ on Storage. Typical questions I might ask the storage team, or the storage team may ask me .

 Do you have  documentation from… Read more

0 comments, 1,270 reads

Posted in SQLServer-DBA on 27 August 2011

Index Disabled and Index Rebuild

I received the following message from a Developer. The message was on a Test SQL Server

 NC_myIndex " on table "VeryLargeTable" (specified in the FROM clause) is disabled or resides in a filegroup which is not online

 The cause of the message was that the index was… Read more

0 comments, 729 reads

Posted in SQLServer-DBA on 25 August 2011

Correctness and stress test

Stress test SQL Server for new hardware

 Download SQLIOSim    from http://download.microsoft.com    and install on server.

When detailing the  file location , use the same as the SQL Server files. SQLIOSim will attempt to simulate the SQL Server database  IO path.

The emphasis is to hammer the system but stay… Read more

0 comments, 660 reads

Posted in SQLServer-DBA on 24 August 2011

Disk IO performance and SQLIO

Performance testing  a new disk subsystem  from a SQL Server perspective is one of the fun aspects of DBA work. Firstly, it’s a great opportunity to influence the optimizations and give valuable feedback to the storage team. Secondly, it’s difficult to simulate the exact workloads and IO patterns. This is… Read more

1 comments, 3,232 reads

Posted in SQLServer-DBA on 23 August 2011

Define IOPs for all database servers


 The storage guy is configuring a new storage system. It includes RAID arrays, SVC, HBA & Fibre Channel configurations.

 He’s asked me to define some configurations for optimal SQL Server performance. Rather than supply him with configurations , I prefer a different approach.

 Create a IOPS per database server… Read more

0 comments, 2,005 reads

Posted in SQLServer-DBA on 22 August 2011

Attach database without log file and rename database

Assuming the database was detached successfully , this t-sql code will attach a mdf file  without a log file . It will also reattach the database with a new database name,.


USE [master]
CREATE DATABASE [myTestDatabase] ON 
( FILENAME = N'E:\Program Files\Microsoft SQL Server\Data\MSSQL.1\MSSQL\Data\myTestDatabase.mdf' ) 

Read more

0 comments, 1,275 reads

Posted in SQLServer-DBA on 19 August 2011

How to report a SQL Server performance problem

You’re experiencing a sudden slow down in SQL Server  performance. Users are complaining .Many reasons could be causing this sudden slowdown .

For SQL Server performance problems, gathering and analyzing data is not enough.  A lot of time can be wasted analyzing data not relevant to the problem.

Before you… Read more

0 comments, 683 reads

Posted in SQLServer-DBA on 18 August 2011

RAID server configuration and disk amounts

Choosing the optimal amount of disks for a RAID server configuration and block sizes can be a challenge.

Typical questions from the SAN\Storage guy to the DBA are:

“How many disks in a RAID configuration?”

“What is the optimal block size?”

“What MB\sec  throughput?” etc

 Here’s a formula to give… Read more

0 comments, 1,064 reads

Posted in SQLServer-DBA on 17 August 2011

Calculate table size with existing data

Calculate the physical size of a SQL Server database table (data and indexes) , if rows exist . These steps present a good estimate

 1)  Use sp_spaceused ‘myTableName’.

 2)  To maintain up to date data, use DBCC UPDATEUSAGE before the sp_spaceused

 For a specific table :


0 comments, 906 reads

Posted in SQLServer-DBA on 16 August 2011