2q - Indexs and Optimize...

Dont know if that is the right group ... but here goes
2 questions:
1. What are the diffenrence on adding 1 column per Index, or adding more
columns per index... i'm a little confused here. Adn waht ORDER BY? is
that just the default ordering rule when doing "ORDER BY column"?
2. Are there any easy why to examine sql queries comming to the server
so its easier to debug what quries are slow ... ? so its possible to
make some optimazations on them ...
kind regards
Mikael SyskaAs far as 1) goes theres a world of difference. I would suggest reading
about indexes, if you have the time 'Inside Sql server 2000' would probably
give you all the answers you need.
2) you can use Profiler tool. Trace queries and possibly filter them by
duration value. If you put over 3000 you'll see only queries that last over
3 seconds...
MC
"Mikael Syska" <news01@.syska.dk> wrote in message
news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Dont know if that is the right group ... but here goes
> 2 questions:
> 1. What are the diffenrence on adding 1 column per Index, or adding more
> columns per index... i'm a little confused here. Adn waht ORDER BY? is
> that just the default ordering rule when doing "ORDER BY column"?
> 2. Are there any easy why to examine sql queries comming to the server so
> its easier to debug what quries are slow ... ? so its possible to make
> some optimazations on them ...
> kind regards
> Mikael Syska|||Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probably
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last over
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding more
>> columns per index... i'm a little confused here. Adn waht ORDER BY? is
>> that just the default ordering rule when doing "ORDER BY column"?
>> 2. Are there any easy why to examine sql queries comming to the server so
>> its easier to debug what quries are slow ... ? so its possible to make
>> some optimazations on them ...
>> kind regards
>> Mikael Syska
>|||Yes, I think so ...
So an index on FirstName DESC, LastName ASC
and the query ...
SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
would are the list after that index ... ASC on FirstName and DESC on
LastName ?
and if I had a other index on FirstName ASC and did a
SELECT Firname, LastName FROM t1 ORDER BY FirstName
it would sort DESC ... right ? if i guess they are grouped ...
But are there any performance gain by doing it that way (then some
columns would be added more than 1 time) ?
best regrads
Mikael Syska
David Markle wrote:
> Mikael:
> When people ask me this question (#1), I always say, look at the phone
> book. You can think of a phone book as being like a 2-way composite
> index on Lastname (Ascending) and Firstname (Ascending), in that order:
> Adams, John
> Bryant, Frank
> Coolidge, Adam
> Coolidge, Calvin
> Coolidge, Zach
> If you ordered it as Lastname (Ascending) and Firstname (Descending):
> Adams, John
> Bryant, Frank
> Coolidge, Zach
> Coolidge, Calvin
> Coolidge, Adam
> If you ordered it as First name (Ascending) ...
> Coolidge, Adam
> Coolidge, Calvin
> Bryant, Frank
> Adams, John
> Coolidge, Zach
> Get the idea?
> MC wrote:
>> As far as 1) goes theres a world of difference. I would suggest
>> reading about indexes, if you have the time 'Inside Sql server 2000'
>> would probably give you all the answers you need.
>> 2) you can use Profiler tool. Trace queries and possibly filter them
>> by duration value. If you put over 3000 you'll see only queries that
>> last over 3 seconds...
>>
>> MC
>> "Mikael Syska" <news01@.syska.dk> wrote in message
>> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding
>> more columns per index... i'm a little confused here. Adn waht ORDER
>> BY? is that just the default ordering rule when doing "ORDER BY column"?
>> 2. Are there any easy why to examine sql queries comming to the
>> server so its easier to debug what quries are slow ... ? so its
>> possible to make some optimazations on them ...
>> kind regards
>> Mikael Syska
>>|||"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test
Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY:([testing].[dbo].[order_test].[fname] ASC,
[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT:([testing].[dbo].[order_test].[fname_idx]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT:([testing].[dbo].[order_test].[fname_idx]), ORDERED
FORWARD)
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
>> Mikael:
>> When people ask me this question (#1), I always say, look at the phone
>> book. You can think of a phone book as being like a 2-way composite
>> index on Lastname (Ascending) and Firstname (Ascending), in that order:
>> Adams, John
>> Bryant, Frank
>> Coolidge, Adam
>> Coolidge, Calvin
>> Coolidge, Zach
>> If you ordered it as Lastname (Ascending) and Firstname (Descending):
>> Adams, John
>> Bryant, Frank
>> Coolidge, Zach
>> Coolidge, Calvin
>> Coolidge, Adam
>> If you ordered it as First name (Ascending) ...
>> Coolidge, Adam
>> Coolidge, Calvin
>> Bryant, Frank
>> Adams, John
>> Coolidge, Zach
>> Get the idea?
>> MC wrote:
>> As far as 1) goes theres a world of difference. I would suggest reading
>> about indexes, if you have the time 'Inside Sql server 2000' would
>> probably give you all the answers you need.
>> 2) you can use Profiler tool. Trace queries and possibly filter them by
>> duration value. If you put over 3000 you'll see only queries that last
>> over 3 seconds...
>>
>> MC
>> "Mikael Syska" <news01@.syska.dk> wrote in message
>> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding
>> more columns per index... i'm a little confused here. Adn waht ORDER
>> BY? is that just the default ordering rule when doing "ORDER BY
>> column"?
>> 2. Are there any easy why to examine sql queries comming to the server
>> so its easier to debug what quries are slow ... ? so its possible to
>> make some optimazations on them ...
>> kind regards
>> Mikael Syska
>>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

2q - Indexs and Optimize...

Dont know if that is the right group ... but here goes
2 questions:
1. What are the diffenrence on adding 1 column per Index, or adding more
columns per index... i'm a little confused here. Adn waht ORDER BY? is
that just the default ordering rule when doing "ORDER BY column"?
2. Are there any easy why to examine sql queries comming to the server
so its easier to debug what quries are slow ... ? so its possible to
make some optimazations on them ...
kind regards
Mikael SyskaAs far as 1) goes theres a world of difference. I would suggest reading
about indexes, if you have the time 'Inside Sql server 2000' would probably
give you all the answers you need.
2) you can use Profiler tool. Trace queries and possibly filter them by
duration value. If you put over 3000 you'll see only queries that last over
3 seconds...
MC
"Mikael Syska" <news01@.syska.dk> wrote in message
news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Dont know if that is the right group ... but here goes
> 2 questions:
> 1. What are the diffenrence on adding 1 column per Index, or adding more
> columns per index... i'm a little confused here. Adn waht ORDER BY? is
> that just the default ordering rule when doing "ORDER BY column"?
> 2. Are there any easy why to examine sql queries comming to the server so
> its easier to debug what quries are slow ... ? so its possible to make
> some optimazations on them ...
> kind regards
> Mikael Syska|||Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probabl
y
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last ove
r
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>|||Yes, I think so ...
So an index on FirstName DESC, LastName ASC
and the query ...
SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
would are the list after that index ... ASC on FirstName and DESC on
LastName ?
and if I had a other index on FirstName ASC and did a
SELECT Firname, LastName FROM t1 ORDER BY FirstName
it would sort DESC ... right ? if i guess they are grouped ...
But are there any performance gain by doing it that way (then some
columns would be added more than 1 time) ?
best regrads
Mikael Syska
David Markle wrote:[vbcol=seagreen]
> Mikael:
> When people ask me this question (#1), I always say, look at the phone
> book. You can think of a phone book as being like a 2-way composite
> index on Lastname (Ascending) and Firstname (Ascending), in that order:
> Adams, John
> Bryant, Frank
> Coolidge, Adam
> Coolidge, Calvin
> Coolidge, Zach
> If you ordered it as Lastname (Ascending) and Firstname (Descending):
> Adams, John
> Bryant, Frank
> Coolidge, Zach
> Coolidge, Calvin
> Coolidge, Adam
> If you ordered it as First name (Ascending) ...
> Coolidge, Adam
> Coolidge, Calvin
> Bryant, Frank
> Adams, John
> Coolidge, Zach
> Get the idea?
> MC wrote:|||"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test
Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY[testing].[dbo].[order_test].[fname] ASC,
[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT[testing].[dbo].[order_test].[fname_id
x]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT[testing].[dbo].[order_test].[fname_id
x]), ORDERED
FORWARD)
[vbcol=seagreen]
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

2005 Failover Cluster SAN drives best practices

I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
currently have two nodes and I am planning on adding more instances to
run on the other node.
First, I am not sure I have setup my existing default instance the best
way for my cluster. I currently only have one 'Physical Disk' in the
cluster group which is a LUN on our SAN. Both the data files and log
files are stored there. What is the best practice to move the logs onto
another spindle? Do I have to just create another LUN?
I am about to add a few more instances. Does each one need to have it's
own LUN?
There is a limited amount of drive letters that I can create. Is there
a way to share a LUN between instances? I seem to remember reading a
feature of 2005 that was related to this but, for the life of me, I
cannot find it again.
Any help would be appreciated!
Thank you
Karl> What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
You should at least put yout transaction log files on a separate LUN from
your data LUN. This doesn't guarantee that your data and log would be placed
on separate spindles inside SAN because both LUNs can be carved from the same
set of disks, but at least it gives you some separation.
> I am about to add a few more instances.
> Does each one need to have it's own LUN?
Each clustered instance will be implemented a separate cluster resource
group with its own disk resource(s). Since you can't have the same read/write
disk as two separate disk resources in different resource groups, each
clustered instance needs its own LUN.
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances?
You can't really share drive letters. But SQL2005 fully supports mounted
volumes in a cluster. These volumes don't have to be assigned separate drive
letters. As long as they are made disk resources, and are mounted under a
root drive letter, you can have multiple volumes in a resource group (or an
instance) with as few as one drive letter per instance.
Linchi
"Karl" wrote:
> I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
> currently have two nodes and I am planning on adding more instances to
> run on the other node.
> First, I am not sure I have setup my existing default instance the best
> way for my cluster. I currently only have one 'Physical Disk' in the
> cluster group which is a LUN on our SAN. Both the data files and log
> files are stored there. What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
> I am about to add a few more instances. Does each one need to have it's
> own LUN?
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances? I seem to remember reading a
> feature of 2005 that was related to this but, for the life of me, I
> cannot find it again.
> Any help would be appreciated!
> Thank you
> Karl
>