Archives: August 2011
“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
Executing the code below in SQL Server 2008 will throw an error message:
BACKUP LOG ‘MyDatabase’ WITH TRUNCATE_ONLY 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 :
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
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
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
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
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
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
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] GO CREATE DATABASE [myTestDatabase] ON ( FILENAME = N'E:\Program Files\Microsoft SQL Server\Data\MSSQL.1\MSSQL\Data\myTestDatabase.mdf' ) FOR ATTACH GO if…
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
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
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 :
DBCC UPDATEUSAGE(‘<DB_NAME>’,‘<TABLE_NAME>’) Read more