Ultimate SQL Performance???

[Ali Alhussein]

hi, I am about to loose faith in MSSQL 6.5 for his reason. I am dealing with 9 GB of data in 9 tables. the server is very very slow. it takes ages to retrieve a simple query from one table, what about from two tables?

Please help , what can I do to improve performace... I have 1152 MB of memory. 45 GB Harddrive. I have increase the memory in the sql configuration to 131072 and the locks to 12000.....still I am getting SQL Server out of lock.. .what am I doing wrong, is the configuration wrong? I do not want to convince myself that MSSQL is not Capable of dealing with such amount of Data... any help from SQL wize

thanks for your help

[Anthony B. Kenitzki]

Microsoft has loads of papers for you to read on SQL performance tuning and optimization:

Buy the SQL Server 6.5 resource guide. I found most of what I needed to know in that book.

There are a good deal of papers somewhere on Microsoft`s web site that you can read for more specific issues (I/O, Memory, etc.)

I have a database server that is right about 20 gigabytes in size and I have had few performance problems outside of the occasional concurrency issue.

Microsoft SQL Server 7.0 is going to be released very soon. If I were a Micosoft SQL Server beginner I would look try using that. SQL 7.0 does a majority of it`s performance tuning by itself, it has a good set a wizards, and it is very beginner friendly.

Anthony B. Kenitzki


On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am about to loose faith in MSSQL 6.5 for his reason. I am dealing
> with 9 GB of data in 9 tables. the server is very very slow. it takes ages
> to retrieve a simple query from one table, what about from two
> tables?

Please help , what can I do to improve performace... I have 1152
> MB of memory. 45 GB Harddrive. I have increase the memory in the sql
> configuration to 131072 and the locks to 12000.....still I am getting SQL
> Server out of lock.. .what am I doing wrong, is the configuration wrong? I
> do not want to convince myself that MSSQL is not Capable of dealing with
> such amount of Data... any help from SQL wize

thanks for your help

[Armando Torres Jr.]

On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am about to loose faith in MSSQL 6.5 for his reason. I am dealing
> with 9 GB of data in 9 tables. the server is very very slow. it takes ages
> to retrieve a simple query from one table, what about from two
> tables?

Please help , what can I do to improve performace... I have 1152
> MB of memory. 45 GB Harddrive. I have increase the memory in the sql
> configuration to 131072 and the locks to 12000.....still I am getting SQL
> Server out of lock.. .what am I doing wrong, is the configuration wrong? I
> do not want to convince myself that MSSQL is not Capable of dealing with
> such amount of Data... any help from SQL wize

thanks for your help

One question! Is this a dedicated SQL Server or does it have many application on it other than SQL such as Exchange, IIS, SMS or any of the others. If it is dedicated, I think your setting are low for memory configuration. I feel that these were the settings when I got to my job and I increased it to 206000 to take on approximately 400 MB of my 512 MB. Remember, these are in 2K pages. If you want to see how much you actually have on SQL, do
dbcc memusage to get a reading of your memory usage. I left my reference books at the job because I have also been fine tuning my system because of bufwait and lazywriter problems.

Also, check your hashbuckets setting. This also may be to low which would cause it to be slow in retreiving data. Do a dbcc sqlperf (hashstats) and look at the longest BUFHASH. It should be less than 4 and to configure this, you figure one hashbucket to 2 or 3 2K pages of memory. Our locks are set at 50000 which was changed by me because the number they placed in this setting was extremely high. It was set at 100000. I know that 50000 is still high but it does not tax my memory or the system so I will leave it for now. To verify what I have told you above, please contact me at my job where I can see my settings and compare them and we can help each other. Call me at 650-652-1300 ext.255. I will log on again tomorrow morning and post what I found out when I got to work.

[David Ray]

On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am about to loose faith in MSSQL 6.5 for his reason. I am dealing
> with 9 GB of data in 9 tables. the server is very very slow. it takes ages
> to retrieve a simple query from one table, what about from two
> tables?

Please help , what can I do to improve performace... I have 1152
> MB of memory. 45 GB Harddrive. I have increase the memory in the sql
> configuration to 131072 and the locks to 12000.....still I am getting SQL
> Server out of lock.. .what am I doing wrong, is the configuration wrong? I
> do not want to convince myself that MSSQL is not Capable of dealing with
> such amount of Data... any help from SQL wize

thanks for your help

++++++++++++++++++++++++++++++++

>it takes ages
> to retrieve a simple query from one table, what about from two
> tables?

Ali,

What is the indexing like on these tables you mention ? Are they suitable for your queries or the applications you use that are executing these queries?

Before you look at server configurations, try a query plan on one of the offending queries and see how it adopts the use of your indexes.

Hope this helps

David Ray
Advali Ltd

[Ali Alhussein]

Thanks for your feedback
Ali

On 10/26/98 5:56:19 PM, Anthony B. Kenitzki wrote:
>

Microsoft has loads of papers for you to read on SQL performance tuning
> and optimization:

Buy the SQL Server 6.5 resource guide. I found most
> of what I needed to know in that book.

There are a good deal of papers
> somewhere on Microsoft`s web site that you can read for more specific
> issues (I/O, Memory, etc.)

I have a database server that is right about
> 20 gigabytes in size and I have had few performance problems outside of the
> occasional concurrency issue.

Microsoft SQL Server 7.0 is going to be
> released very soon. If I were a Micosoft SQL Server beginner I would look
> try using that. SQL 7.0 does a majority of it`s performance tuning by
> itself, it has a good set a wizards, and it is very beginner
> friendly.

Anthony B. Kenitzki


On 10/26/98 5:18:21 PM, Ali Alhussein
> wrote:
> hi, I am about to loose faith in MSSQL 6.5 for his reason. I am
> dealing
> with 9 GB of data in 9 tables. the server is very very slow. it
> takes ages
> to retrieve a simple query from one table, what about from
> two
> tables?

Please help , what can I do to improve performace... I
> have 1152
> MB of memory. 45 GB Harddrive. I have increase the memory in
> the sql
> configuration to 131072 and the locks to 12000.....still I am
> getting SQL
> Server out of lock.. .what am I doing wrong, is the
> configuration wrong? I
> do not want to convince myself that MSSQL is
> not Capable of dealing with
> such amount of Data... any help from SQL
> wize

thanks for your help

[Ali Alhussein]

Hi, thanks for your help, I will try to change the configuration and hopfully will get better performance, I hope you do not mind calling you and ask you for extra help....Thanks alot

Ali

On 10/26/98 10:03:26 PM, Armando Torres Jr. wrote:
> On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am about to loose
> faith in MSSQL 6.5 for his reason. I am dealing
> with 9 GB of data in 9
> tables. the server is very very slow. it takes ages
> to retrieve a
> simple query from one table, what about from two
> tables?

Please help
> , what can I do to improve performace... I have 1152
> MB of memory. 45
> GB Harddrive. I have increase the memory in the sql
> configuration to
> 131072 and the locks to 12000.....still I am getting SQL
> Server out of
> lock.. .what am I doing wrong, is the configuration wrong? I
> do not
> want to convince myself that MSSQL is not Capable of dealing with
> such
> amount of Data... any help from SQL wize

thanks for your help

One
> question! Is this a dedicated SQL Server or does it have many application
> on it other than SQL such as Exchange, IIS, SMS or any of the others. If it
> is dedicated, I think your setting are low for memory configuration. I feel
> that these were the settings when I got to my job and I increased it to
> 206000 to take on approximately 400 MB of my 512 MB. Remember, these are in
> 2K pages. If you want to see how much you actually have on SQL, do
dbcc
> memusage to get a reading of your memory usage. I left my reference books
> at the job because I have also been fine tuning my system because of
> bufwait and lazywriter problems.

Also, check your hashbuckets setting.
> This also may be to low which would cause it to be slow in retreiving data.
> Do a dbcc sqlperf (hashstats) and look at the longest BUFHASH. It should be
> less than 4 and to configure this, you figure one hashbucket to 2 or 3 2K
> pages of memory. Our locks are set at 50000 which was changed by me because
> the number they placed in this setting was extremely high. It was set at
> 100000. I know that 50000 is still high but it does not tax my memory or
> the system so I will leave it for now. To verify what I have told you
> above, please contact me at my job where I can see my settings and compare
> them and we can help each other. Call me at 650-652-1300 ext.255. I will
> log on again tomorrow morning and post what I found out when I got to work.

[Ali Alhussein]

Hi David, well, this table does not have any primary key or index. I just pulled it from the main frame ( txt file ) .... I realize that there are alot of duplicate records.... I am not sure what is the best solution to massage this table so I can have a fast query output... what I am thinking is to run a distinct query and move the query result into a new table, then make some index on order_no field.. then run a query and see how performance is improved... Honestly, I am not sure if this will be the right way to do it, I know it will take along long time to get the query result.... any bright ideas and hay, thanks for your help too.....

There are agreat people in this news group that are really helpfull. I am glad to be part of this group

regards

Ali
On 10/27/98 4:01:19 AM, David Ray wrote:
> On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am about to loose
> faith in MSSQL 6.5 for his reason. I am dealing
> with 9 GB of data in 9
> tables. the server is very very slow. it takes ages
> to retrieve a
> simple query from one table, what about from two
> tables?

Please help
> , what can I do to improve performace... I have 1152
> MB of memory. 45
> GB Harddrive. I have increase the memory in the sql
> configuration to
> 131072 and the locks to 12000.....still I am getting SQL
> Server out of
> lock.. .what am I doing wrong, is the configuration wrong? I
> do not
> want to convince myself that MSSQL is not Capable of dealing with
> such
> amount of Data... any help from SQL wize

thanks for your help
>

++++++++++++++++++++++++++++++++

>it takes ages
> to retrieve a
> simple query from one table, what about from two
>
> tables?

Ali,

What is the indexing like on these tables you mention ?
> Are they suitable for your queries or the applications you use that are
> executing these queries?

Before you look at server configurations, try a
> query plan on one of the offending queries and see how it adopts the use of
> your indexes.

Hope this helps

David Ray
Advali Ltd

[David Ray]

On 10/27/98 7:39:29 AM, Ali Alhussein wrote:
> Hi David, well, this table does not have any primary key or index. I just
> pulled it from the main frame ( txt file ) .... I realize that there are
> alot of duplicate records.... I am not sure what is the best solution to
> massage this table so I can have a fast query output... what I am thinking
> is to run a distinct query and move the query result into a new table, then
> make some index on order_no field.. then run a query and see how
> performance is improved... Honestly, I am not sure if this will be the
> right way to do it, I know it will take along long time to get the query
> result.... any bright ideas and hay, thanks for your help too.....

There
> are agreat people in this news group that are really helpfull. I am glad to
> be part of this group

+++++++++++++++++++++++++++++++

Ali,

I think you`ve hit one nail on the head. Intially, I`d do what you`ve already suggested, i.e.

1. Iron out all duplicates - select a distinct copy of the table into a new table.

2. Establish what your query needs (from its where clause) - I think you said `order_no`. Try a unique clustered index on this column.

3. Try your query from there.

What you have to remember is the offset of time between creating the index, or waiting for your query to return results without an index. If the query is run repetitively, then creating the index is a must and very cost effective for processing / production time.

One other thing I`ve found to be of help. If you are going to pull this information from the mainframe frequently, then the duplication problem should be resolved at source i.e. on the mainframe.

Regards


David Ray
Advali Ltd.


regards

Ali
On 10/27/98 4:01:19 AM, David
> Ray wrote:
> On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am
> about to loose
> faith in MSSQL 6.5 for his reason. I am dealing
> with
> 9 GB of data in 9
> tables. the server is very very slow. it takes ages
>
> to retrieve a
> simple query from one table, what about from two
>
> tables?

Please help
> , what can I do to improve performace... I have
> 1152
> MB of memory. 45
> GB Harddrive. I have increase the memory in
> the sql
> configuration to
> 131072 and the locks to 12000.....still I
> am getting SQL
> Server out of
> lock.. .what am I doing wrong, is the
> configuration wrong? I
> do not
> want to convince myself that MSSQL
> is not Capable of dealing with
> such
> amount of Data... any help from
> SQL wize

thanks for your help
>
>

++++++++++++++++++++++++++++++++

>it takes ages
> to retrieve a
>
> simple query from one table, what about from two
>
>
> tables?

Ali,

What is the indexing like on these tables you mention ?
>
> Are they suitable for your queries or the applications you use that are
>
> executing these queries?

Before you look at server configurations,
> try a
> query plan on one of the offending queries and see how it adopts
> the use of
> your indexes.

Hope this helps

David Ray
Advali Ltd

[Ali Alhussein]

Dave, thanks for your input. I will see how things will and let you know


Ali

On 10/27/98 9:30:39 AM, David Ray wrote:

> On 10/27/98 7:39:29 AM, Ali Alhussein wrote:
> Hi David, well, this table
> does not have any primary key or index. I just
> pulled it from the main
> frame ( txt file ) .... I realize that there are
> alot of duplicate
> records.... I am not sure what is the best solution to
> massage this
> table so I can have a fast query output... what I am thinking
> is to run
> a distinct query and move the query result into a new table, then
> make
> some index on order_no field.. then run a query and see how
> performance
> is improved... Honestly, I am not sure if this will be the
> right way to
> do it, I know it will take along long time to get the query
> result....
> any bright ideas and hay, thanks for your help too.....

There
> are
> agreat people in this news group that are really helpfull. I am glad to
>
> be part of this group

+++++++++++++++++++++++++++++++

Ali,

I
> think you`ve hit one nail on the head. Intially, I`d do what you`ve already
> suggested, i.e.

1. Iron out all duplicates - select a distinct copy of
> the table into a new table.

2. Establish what your query needs (from
> its where clause) - I think you said `order_no`. Try a unique clustered
> index on this column.

3. Try your query from there.

What you have to
> remember is the offset of time between creating the index, or waiting for
> your query to return results without an index. If the query is run
> repetitively, then creating the index is a must and very cost effective for
> processing / production time.

One other thing I`ve found to be of help.
> If you are going to pull this information from the mainframe frequently,
> then the duplication problem should be resolved at source i.e. on the
> mainframe.

Regards


David Ray
Advali Ltd.


regards

Ali
On
> 10/27/98 4:01:19 AM, David
> Ray wrote:
> On 10/26/98 5:18:21 PM, Ali
> Alhussein wrote:
> hi, I am
> about to loose
> faith in MSSQL 6.5 for
> his reason. I am dealing
> with
> 9 GB of data in 9
> tables. the
> server is very very slow. it takes ages
>
> to retrieve a
> simple
> query from one table, what about from two
>
> tables?

Please help
>
> , what can I do to improve performace... I have
> 1152
> MB of
> memory. 45
> GB Harddrive. I have increase the memory in
> the sql
>
> configuration to
> 131072 and the locks to 12000.....still I
> am
> getting SQL
> Server out of
> lock.. .what am I doing wrong, is the
>
> configuration wrong? I
> do not
> want to convince myself that MSSQL
>
> is not Capable of dealing with
> such
> amount of Data... any help
> from
> SQL wize

thanks for your help
>
>
>

++++++++++++++++++++++++++++++++

>it takes ages
> to retrieve a
>
>
> simple query from one table, what about from two
>
>
>
> tables?

Ali,

What is the indexing like on these tables you mention ?
>
>
> Are they suitable for your queries or the applications you use that
> are
>
> executing these queries?

Before you look at server
> configurations,
> try a
> query plan on one of the offending queries
> and see how it adopts
> the use of
> your indexes.

Hope this
> helps

David Ray
Advali Ltd

[Ali Alhussein]

Hi, here is the result of the bdcc sqlperf(hashstats)... I guess it is >4 ,,, how can I reduce it and what doest this mean I do not understand this table... how does it relate to performance?
thanks for your help
Ali

Type Items Buckets Chains Longest Average
-------- ----------- ----------- ----------- ----------- ------------------------
BUFHASH 159328 7993 7993 24 19.9334
DESHASH 175 256 71 8 2.46479

n 10/26/98 10:03:26 PM, Armando Torres Jr. wrote:
> On 10/26/98 5:18:21 PM, Ali Alhussein wrote:
> hi, I am about to loose
> faith in MSSQL 6.5 for his reason. I am dealing
> with 9 GB of data in 9
> tables. the server is very very slow. it takes ages
> to retrieve a
> simple query from one table, what about from two
> tables?

Please help
> , what can I do to improve performace... I have 1152
> MB of memory. 45
> GB Harddrive. I have increase the memory in the sql
> configuration to
> 131072 and the locks to 12000.....still I am getting SQL
> Server out of
> lock.. .what am I doing wrong, is the configuration wrong? I
> do not
> want to convince myself that MSSQL is not Capable of dealing with
> such
> amount of Data... any help from SQL wize

thanks for your help

One
> question! Is this a dedicated SQL Server or does it have many application
> on it other than SQL such as Exchange, IIS, SMS or any of the others. If it
> is dedicated, I think your setting are low for memory configuration. I feel
> that these were the settings when I got to my job and I increased it to
> 206000 to take on approximately 400 MB of my 512 MB. Remember, these are in
> 2K pages. If you want to see how much you actually have on SQL, do
dbcc
> memusage to get a reading of your memory usage. I left my reference books
> at the job because I have also been fine tuning my system because of
> bufwait and lazywriter problems.

Also, check your hashbuckets setting.
> This also may be to low which would cause it to be slow in retreiving data.
> Do a dbcc sqlperf (hashstats) and look at the longest BUFHASH. It should be
> less than 4 and to configure this, you figure one hashbucket to 2 or 3 2K
> pages of memory. Our locks are set at 50000 which was changed by me because
> the number they placed in this setting was extremely high. It was set at
> 100000. I know that 50000 is still high but it does not tax my memory or
> the system so I will leave it for now. To verify what I have told you
> above, please contact me at my job where I can see my settings and compare
> them and we can help each other. Call me at 650-652-1300 ext.255. I will
> log on again tomorrow morning and post what I found out when I got to work.

[Ali Alhussein]

hi,Armando I tried to send you an email and could not go throgh, it seems that the email address you gave me is not correct. Would you please resend your correct email address. Thanks.. also I did not get the white paper you told me about ...

Anyway, I want to thank you for helping me in this issue

Ali