Saturday July 23rd 2010
Back to creating databases...
Friday July 22nd 2010
There is one kind of index that does never get used unless especially requested:
Full Text Indexes.
I was involved in a case where a involuntary SQL Server DBA (developer that runs
also the production) created an full text index for a column. In theory a good
thing. Unfortunately the did not change the query to actually use and was
wondering why it did not improve the query performance.
To actually use a full text index you must change a query from
SELECT
* FROM MyTable
WHERE ColumnName LIKE
'%MyText%'
to
SELECT
* FROM MyTable
WHERE CONTAINS
((ColumName),
'MyText')
Thursday July 22nd 2010
The Mentors form SolidQ started an online magazine about SQL Server, check it
out at
http://www.solidq.com/sqj
i found the articles very interesting
Wednesday July 21st 2010
Over the next few days I will elaborate a little bit about creating a database.
Sounds like a simple task but a lot
can go wrong.
Let's start simple
CREATE
DATABASE MyDatabase;
This command creates a database based on the model database in the configured
default directory.
If the model database was never configured the new database will have one
logfile (ldf) and one datafile (mdf)
Over the next days while I prepare my next presentation for the user groups and
conferences about creating databases i will post my ideas here
Tuesday July 20th 2010
Multi Location backup the thrid
A little bit more about backups to multiple locations. The data from your
database is read just once, this is an advantage because the IO on the database
is kept to a minimum. But that also means that the backup will take as long as
is takes to write to the slowest target location.
You can backup full, differential and log backups to multtiple locations.
I do not backup full backups to two locations (you can alsways take the backup
before the last to start your restore if you lose the last one) but I backup my
logs to two locations
Monday July 19th 2010
Backup to multiple locations
So lets explain how to backup to two locations at once. First the bad news this
is an Enterprise (SQL Server 2005, 2008 and 2008R2) and Datacenter Edition (SQL
Server 2008R2) only feature.
If the backup to one location looks like this
BACKUP
DATABASE MyDatabase
TO
DISK =
'D:\BACKUP\MyDatabase.bak'
Backing up to two locations looks
like this
BACKUP
DATABASE tg
TO DISK
=
'D:\BACKUP\MyDatabase.bak'
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup2.bak'
WITH FORMAT
The WITH FORMAT is required even if the backup files do not exist
You can go wild and backup to up to 8 locations at the same time.
BACKUP
DATABASE tg
TO DISK
=
'D:\BACKUP\MyDatabase.bak
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup2.bak'
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup3.bak'
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup4.bak'
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup5.bak'
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup6.bak'
MIRROR
TO DISK
=
'E:\BACKUP\MyDBBackup7.bak'
MIRROR
TO
DISK =
'E:\BACKUP\MyDBBackup8.bak'
WITH FORMAT
I would not recommend that because the feature does not work as you might
expect. It works like any other transaction in the database all or nothing. This
means if one of your locations is unavailable the backup does not start and if
one location fails during the backup the whole backup operation fails.
Friday July 16th 2010
Less is more - sometimes
The basic idea of having more than one backup is basically a good thing. A few
weeks ago a stumbled across a solution that was based on a good intention but did not work as
expected
The DBA had the good idea to have more than one backup of its databases (good
idea). His backup concept was a combination of full, differential and log
backups, so good so far. But he also backed up everything twice (to have 2
copies) so the backup sequence was
The idea was to have the following
Full Backup FA
A and B are the backup servers
Full Backup FB
Log Backup LA1
Log Backup LB1
Diff Backup DA1
Diff Backup DB1
Log Backup LA2
Log Backup LB2
Log Backup LA3
Log Backup LB3
Diff Backup DA2
Diff Backup DB2
and so on
The problem is that the actual sequence the DBA created was
Full Backup FA
Full Backup FB
Log Backup LA1
Log Backup LB2
Diff Backup DA1
Diff Backup DB2
Log Backup LA3
Log Backup LB4
Log Backup LA5
Log Backup LB6
Diff Backup DA(B)3
Diff Backup DB4
and so on
Meaning that the second Full backup rendered the first one useless for all
differential backups and placing each other log file on a different fileserver
does not help either, if for example Server B fails we are left with
Full Backup FA
can be restored
Log Backup LA1
can be restored on top of FA
Diff Backup DA1
is based on FB
Log Backup LA3
LB2 is missing
Log Backup LA5
LB2 is missing
Diff Backup DA3
is based in FB
So we are down to the first log backup after the full, everything later cant be
used
To make this work you need to use the either the MIRROR TO feature of the
enterprise edition or copy the backups with a file copy task
I will explain the MIRROR TO option of the backup command in my next blog
Thursday July 15th 2010
How to assign a text containing
a single quote to a char, varchar, nchar or nvarchar valiable or colum
Sometimes the need to assign a
text like "She's the boss" to a text column or variable arises.
DECLARE @MyText AS NVARCHAR(100)
= 'She's the boss';
You can already see on the
syntax coloring that something is wrong. To put a single quote inside a string
literal you must precede it with another single quote
DECLARE
@MyText AS NVARCHAR(100)
= 'She''s the boss';
If you run the whole
batch below
DECLARE
@MyText AS NVARCHAR(100)
= 'She''s the boss';
SELECT
@MyText;
You will see it produces the
desired result: She's the boss
To give you another example the
text ‘I a am between single quotes’ would look like this
DECLARE
@MyText AS NVARCHAR(100)
= '''I a am between
single quotes''';
SELECT
@MyText;
Wednesday July 14th 2010
What issues might arise when you consolidate two two-node active/passive clusters to one three-node active/active/passive
cluster
In principal I do not see a no go reason, from a financial, space and power
consumption point it makes total sense but as always with SQL Server it depends.
There are a few things you should consider
-
If you run
more than one instance of SQL Server on a cluster you must use named instances
on different than the default port for all but the first instance (it is
possible to configure them on different IP's and then use the default port on
all instances but that is a very complex task in a cluster and can to lead a lot
of problems). If you have no problem with named instances on different ports
which also means that you have to changing the connection strings of some
applications you are fine. I still recommend to to use a fixed and not a dynamic
port for all instances
-
Make sure
the heartbeat network is redundant (on a two-node cluster its very likely a
cross over network cable was used, with 3 or more nodes you need to use a
network device (hub, switch) between the nodes which might if you have only one
heartbeat network become a single point of failure.
I recommend to use 2 heartbeat networks (either also configure the access
network for heartbeat usage or as I do on my systems use a second exclusive
heartbeat network on a separate network switch) Also note that you should never
use the same network equipment for the access and the heartbeat network.
-
Availability: For the availability issue it depends what your SLA states. What
is the worst case scenario?
-
On a two-node
cluster if one node fails the second one takes over - no problem.
-
On two two-node clusters if
one server of each cluster fails we are good to
-
On two two-node clusters if both servers of one cluster fail the
cluster is down
-
On a three-node cluster if
one server fails, we failover to the passive instance and everything is fine
-
On a three-node cluster two
servers fail , both instances might run on the third node sharing the resources
If we compare cases a, b and d we have the same availability on both solutions
If we compare case c, e we have better availability on a 3 node cluster
If we compare case b and e we have better performance on two two-node clusters
mostly because the two instances will fight for memory on the left over node.
You could mitigate that by giving the passive node more memory
-
Compatibility: On a multi node cluster all instances should have the same SQL
Server version (it is not a requirement but you avoid a lot of issues and not
supported situations). So if you want to upgrade one of the instances you
should/must upgrade them all, meaning that all applications using one of the
instances must work with the new version
If none of this minor issues is a problem then yes go for it consolidate the
clusters
Tuesday July 13th 2010
A little bit of history today, I tried to collect all the codenames for SQL
Server, if someone knows the missing ones please email them to me
mailto:tg@grohser.com.
|
SQL Server Version |
Nickname / Codename |
Remarks |
|
1.0 |
|
|
|
4.21 |
|
|
|
6.0 |
SQL95 |
Enterprise Manager was named Starfighter |
|
6.5 |
Hydra |
|
|
7.0 |
Sphinx |
|
|
2000 |
Shilo |
Reporteing Service was named Rosetta |
|
2000 IA64 |
Liberty |
|
|
2005 |
Yukon |
|
|
2008 |
Katmai |
|
|
2008 R2 |
Kilimanjaro
|
Due to the long name KJ was used a lot too |
|
SQL 11 |
Denali |
|
|
|
|
|
Monday July 12th 2010
Always set the max memory configuration on your systems even if SQL Server is
the only application running on the server. Even if SQL Server in general
manages memory very well sometimes a bad combination of concurrent queries and
logins, combined with a backup running at the same time can cause out of memory
situations if the OS does not have enough memory. SQL Server will try to back
off but it might be to late.
My recomendations if SQL Server is the only application (as of today for SQL
2005 to 2008R2 - I might change my mind):
up to 2 GB leave at least 512 MB to the OS - upgrade memory
up to 4 GB levae 750MB to 1 GB to the OS (later value if you have a lot of
logins)
up to 8 GB leave 1GB to the OS
up to 16 GB leave 2 GB to the OS
up to 32 GB leave 4 GB to the OS
up to 64 GB leave 8 GB to the OS
up to 128 GB leave 16GB to the OS
up to 256 GB levae 32GB to the OS
up to 512 GB levae 48GB to the OS
up to 1TB leave 64GB to the OS
up to 2TB leave 96GB to the OS
Sunday, July 11th 2010
Just finnised going over the test result numbers on the Nehalem and Nahelem EX
boxes I tested, Hyper Threading (HT) is still not heping SQL Server to run
faster (all the workloads I tested did run slower with HT enabled).
Thursday, July 8th 2010
Today I found the ultimate reason to finally migrate all SQL 7.0
installations to a more resent version (preferably SQL 2008 R2): I visited the
Heinz Nixdorf Computer Museum and one of the displays was a shrink wrapped box
of SQL Server 7.0...
So if you still have SQL Server 7.0 in your infrastructure don't
be surprised if someone calls it a museum...
Thursday, July 8th 2010
Just finished a performance test on a 4 way Nehalem EX based
system (32 cores), PCIe based solid state device and lots of RAM, first time I
saw more than 200.000 batch requests per second - nice (workload mix for all
interested: a little bit more than
6000 deletes, 24000 inserts, 48.000 updates and 120.000 select per second -
software used to create the workload www.sqlstress.com - SQL Server used SQL
2008 R2 - x64 - enterprise edition
cumulative update 1 - needed 14 eight core clients each one simulating 1024
concurrent users to push it to the limit).
Wednesday , July 7th 2010
More than 10.000 sustained log flushes per second on an OLTP
style workload, mostly updates, peaks got as high as 14.000 per second and
believe it or not the log drive was not the bottleneck - I hope I can publish de
details about the hardware soon.
Tuesday, July 6th 2010
SQL Server 2008 R2 lazy writer appears to be more aggressive than
on SQL 2008 - I will investigate that further and keep you posted
Monday, July 5th 2010
Welcome
Over the last year I spent a lot of time speaking at conferences, lots of people
asked me to start also blogging about all the nice things i find out about SQL
Server. So I finally decided to do so. Hope you enjoy it. If yoy have comments
or questions you can ask me:
mailto:tg@grohser.com.