Slow SQL-server

Slow SQL-server

How can I get a list of settings on my SQL-server - 2005 version ?

Best regards
KSor

date: Fri, 11 Jul 2008 08:05:37 +0200 author: KSor

Re: Slow SQL-server

What settings? There are  a huge amount of settings, ranging from the instance level, through the 
database, table and index level (etc). You can read up on things like sp_configure, sys.databases, 
sp_indexoption, sp_tableoption, sp_procoption etc. But it is easier to tell you anything concrete if 
we know what settings you are looking for.

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"KSor"  wrote in message 
news:eZqR6wx4IHA.4448@TK2MSFTNGP05.phx.gbl...
> How can I get a list of settings on my SQL-server - 2005 version ?
>
> Best regards
> KSor
>
>

date: Fri, 11 Jul 2008 08:34:25 +0200 author: Tibor Karaszi

Re: Slow SQL-server

"Tibor Karaszi"  skrev i 
meddelelsen news:7A77127D-7AB6-471B-BA2F-AA5049134E78@microsoft.com...
> What settings? There are  a huge amount of settings, ranging from the 
> instance level, through the database, table and index level (etc). You can 
> read up on things like sp_configure, sys.databases, sp_indexoption, 
> sp_tableoption, sp_procoption etc. But it is easier to tell you anything 
> concrete if we know what settings you are looking for.
>
> -- 
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
Some settings witch - by changing them - maybe can give me a better 
performance !

I have tried to index the fields I use in my "WHERE xxxx" (xxxx - field is 
now indexed) no impact on the performance at all !

Often it's only one field in the "WHERE" - a boolean - to show if the record 
is active or not !
My former college implemented this very simple (=stupid) way to "delete" 
records and I'm now working on getting all in-active records in fact deleted 
and then delete the "WHERE active=1" in nearly all my stored procedures - 
will this give some more performance ?

I have about 40 tables with from 5 to 4000 records each - and one of them up 
to 20.000 records.

The structure is well normalized and some relations has "cashade deleting" 
set.

8-10 users - it should be a "peace of cake" for the SQL-server I think - but 
it's so slooooooow !

Any ideas what to do ?

Best regards
KSor

date: Fri, 11 Jul 2008 17:53:42 +0200 author: KS keld.SLET_DETTE

Re: Slow SQL-server

Well, in your case there is not just one easy thing to do to gain 
performance to your database.

You'll need to check your indexes (this is not something just creating 
indexes, you may need to drop some of them if they are useless so you'll 
gain performance and storage space)

You may need to tune your queries. Investigate the codes written in your 
Stored Procedures, Views or Functions.

Check your constraints (Triggers, Rules, Defaults...)

Check your databases' statistics...

This is a performance tuning operation so you need a professional to perform 
these actions if you are not qualified on this product.

-- 
Ekrem Önsoy




"KS soerensen@os.dk>" <keld.<SLET_DETTE> wrote in message 
news:6F9001F6-BDC4-46BB-9CE1-F6D34C3738E8@microsoft.com...
> "Tibor Karaszi"  skrev i 
> meddelelsen news:7A77127D-7AB6-471B-BA2F-AA5049134E78@microsoft.com...
>> What settings? There are  a huge amount of settings, ranging from the 
>> instance level, through the database, table and index level (etc). You 
>> can read up on things like sp_configure, sys.databases, sp_indexoption, 
>> sp_tableoption, sp_procoption etc. But it is easier to tell you anything 
>> concrete if we know what settings you are looking for.
>>
>> -- 
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
> Some settings witch - by changing them - maybe can give me a better 
> performance !
>
> I have tried to index the fields I use in my "WHERE xxxx" (xxxx - field is 
> now indexed) no impact on the performance at all !
>
> Often it's only one field in the "WHERE" - a boolean - to show if the 
> record is active or not !
> My former college implemented this very simple (=stupid) way to "delete" 
> records and I'm now working on getting all in-active records in fact 
> deleted and then delete the "WHERE active=1" in nearly all my stored 
> procedures - will this give some more performance ?
>
> I have about 40 tables with from 5 to 4000 records each - and one of them 
> up to 20.000 records.
>
> The structure is well normalized and some relations has "cashade deleting" 
> set.
>
> 8-10 users - it should be a "peace of cake" for the SQL-server I think - 
> but it's so slooooooow !
>
> Any ideas what to do ?
>
> Best regards
> KSor

date: Tue, 15 Jul 2008 13:50:06 +0300 author: Ekrem Önsoy

Re: Slow SQL-server

As Ekrem stated you need to find the actual bottleneck before you can start 
addressing the issues otherwise you could be putting effort into the wrong 
places. Have you looked at the wait stats to see what you are waiting on 
most?  As for your indexes I can't believe that the only argument in the 
where clause is "Active = 1".  I can see why that is there but you must be 
searching on some other criteria other than the row is deleted or not. In 
any case an index on a bit column will rarely be useful since there 
selectivity is usually low. In this case most rows should be active so the 
index will be useless by itself.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"KS soerensen@os.dk>" <keld.<SLET_DETTE> wrote in message 
news:6F9001F6-BDC4-46BB-9CE1-F6D34C3738E8@microsoft.com...
> "Tibor Karaszi"  skrev i 
> meddelelsen news:7A77127D-7AB6-471B-BA2F-AA5049134E78@microsoft.com...
>> What settings? There are  a huge amount of settings, ranging from the 
>> instance level, through the database, table and index level (etc). You 
>> can read up on things like sp_configure, sys.databases, sp_indexoption, 
>> sp_tableoption, sp_procoption etc. But it is easier to tell you anything 
>> concrete if we know what settings you are looking for.
>>
>> -- 
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
> Some settings witch - by changing them - maybe can give me a better 
> performance !
>
> I have tried to index the fields I use in my "WHERE xxxx" (xxxx - field is 
> now indexed) no impact on the performance at all !
>
> Often it's only one field in the "WHERE" - a boolean - to show if the 
> record is active or not !
> My former college implemented this very simple (=stupid) way to "delete" 
> records and I'm now working on getting all in-active records in fact 
> deleted and then delete the "WHERE active=1" in nearly all my stored 
> procedures - will this give some more performance ?
>
> I have about 40 tables with from 5 to 4000 records each - and one of them 
> up to 20.000 records.
>
> The structure is well normalized and some relations has "cashade deleting" 
> set.
>
> 8-10 users - it should be a "peace of cake" for the SQL-server I think - 
> but it's so slooooooow !
>
> Any ideas what to do ?
>
> Best regards
> KSor

date: Tue, 15 Jul 2008 09:39:05 -0400 author: Andrew J. Kelly

Re: Slow SQL-server

"Andrew J. Kelly"  skrev i en meddelelse 
news:edsdnAo5IHA.3684@TK2MSFTNGP05.phx.gbl...
> As Ekrem stated you need to find the actual bottleneck before you can 
> start addressing the issues otherwise you could be putting effort into the 
> wrong places. Have you looked at the wait stats to see what you are 
> waiting on most?  As for your indexes I can't believe that the only 
> argument in the where clause is "Active = 1".  I can see why that is there 
> but you must be searching on some other criteria other than the row is 
> deleted or not. In any case an index on a bit column will rarely be useful 
> since there selectivity is usually low. In this case most rows should be 
> active so the index will be useless by itself.
>
Thanks Andrew !

I have changed the stupid "DELETE" of my former collegea to a REAL deleting 
of records instead of just setting a field "Active = 0" and now I wanna 
delete all his WHERE clauses with his "Active=1" - and in fact EVERY stored 
procedure HAS this "Active=1" !

How can I see this "wait stats" ?

Best regards
KSor, Denmark

date: Tue, 5 Aug 2008 15:57:54 +0200 author: KSor

Re: Slow SQL-server

Well if you are a subscriber to SQL Magazine you can read my article here:
http://www.sqlmag.com/Article/ArticleID/96746/sql_server_96746.html

But this should get you started as well:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx


-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"KSor"  wrote in message 
news:eqnnaNw9IHA.5684@TK2MSFTNGP05.phx.gbl...
> "Andrew J. Kelly"  skrev i en meddelelse 
> news:edsdnAo5IHA.3684@TK2MSFTNGP05.phx.gbl...
>> As Ekrem stated you need to find the actual bottleneck before you can 
>> start addressing the issues otherwise you could be putting effort into 
>> the wrong places. Have you looked at the wait stats to see what you are 
>> waiting on most?  As for your indexes I can't believe that the only 
>> argument in the where clause is "Active = 1".  I can see why that is 
>> there but you must be searching on some other criteria other than the row 
>> is deleted or not. In any case an index on a bit column will rarely be 
>> useful since there selectivity is usually low. In this case most rows 
>> should be active so the index will be useless by itself.
>>
> Thanks Andrew !
>
> I have changed the stupid "DELETE" of my former collegea to a REAL 
> deleting of records instead of just setting a field "Active = 0" and now I 
> wanna delete all his WHERE clauses with his "Active=1" - and in fact EVERY 
> stored procedure HAS this "Active=1" !
>
> How can I see this "wait stats" ?
>
> Best regards
> KSor, Denmark
>
>

date: Tue, 5 Aug 2008 10:28:51 -0400 author: Andrew J. Kelly

Re: Slow SQL-server

"Andrew J. Kelly"  skrev i meddelelsen 
news:enhbTew9IHA.2332@TK2MSFTNGP03.phx.gbl...
> Well if you are a subscriber to SQL Magazine you can read my article here:
> http://www.sqlmag.com/Article/ArticleID/96746/sql_server_96746.html
>
> But this should get you started as well:
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
>
>
Ones again - Thanks Andrew !

Best regards
KSor, Denmark

date: Tue, 5 Aug 2008 18:18:01 +0200 author: KS keld.SLET_DETTE