Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Server Performance

Author  Topic 

yalgaar
Starting Member

43 Posts
Posted - 2006-04-12 : 15:59:24
We have an application that uses SQL 2000 server. I am almost certain all the performance issues we are having are due to the SQL server. I really need to confirm this.

Where do I start? What should I check?

Please help.

Thanks

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-04-12 : 16:23:19
For starters; kick off profiler. (you may just do Start->Run-> type "profiler" and hit enter, start a new trace.)
Make sure that you have the events:
StoredProcedures: RPC:Completed and TSQL: SQL:BatchCompleted
And the column: Duration

watch the trace as you run the application, you can see what the application sends to the sql server, and how long the statements take.


rockmoose
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-12 : 16:35:23
Thanks for your help. I will create the trace. Few more questions:

1) Once I get the trace. How do I read it? What can I do with it?

2) Can I export the trace to Excel?

3)How much duration is normal? How much duration is long?

4) If I find several entries that takes a long time, what does that mean to me?
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-04-12 : 16:58:51
The old Bill Graziano posted a utility: [url]http://weblogs.sqlteam.com/billg/archive/2006/04/07/9555.aspx[/url]
that reads in trace files and present them in a "readable" manner.

You may want to try that out.

1) you can either just analyze the trace visually to detect hotspots and bad-running queries.
otherwise you may save the trace to a file (.trc) or a sql table.
These you may import to excel, or use sql to analyze further.

2) yes if you save it to a file or a database. (when you define the trace there are options where you can choose this)

3) umh, well it depends ... the majority of queries should be just about instantaneous < 10 ms. Look at queries taking more than 5000ms to begin with, these can in many cases be optimized with proper indexes.

4) That is good, see what tables these queries are querying. copy the sql from profiler into query analyzer, and analyze the the query plan.
You might want to use the index wizard if you are not so well versed in reading query plans. (scans are bad, seeks are good )
Adding a good index can make a HUGE difference.


Just by watching the profiler trace, you might see how the application behaves, and if it is doing a lot of "unintelligent" stuff like sending the same query several times, or doing loops instead of retreiving batches of data etc...
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-12 : 17:21:10
I just ran the trace to a table and had the users use the application for 5 min. While they were using the application, they experienced several white-outs and slowness which was good, exactly the way I wanted it.

Now looking at the trace table,

I found out that there are:
46 rows with duration more then 5,000ms
24 rows with duration more then 50,000ms
2 rows with duration more then 300,000ms

What does the above results tell me?
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-04-12 : 17:39:36
The rows with more than 50,000ms are likely to cause timeouts in the application resulting in whiteouts.
The rows with more than 5,000ms will certainly make the application seem slow as they have to wait for more than 5 seconds just to get the answer from the database.

I hope you have the TextData column in you trace as well.
(I did mention the Duration column earlier in the thred, and I apologize if I forgot to explicitly mention the TextData column)
Anyway, the TextData column will show the SQL statement that was sent to Sql Server.
Look at the SQL statement's in the TextData column and see if you can find which statements are the worst performing.

Take those statements and run in query analyzer, and see if you can optimize them.
By examining the query plan, and using the index wizard.

If you are new on sql-server, I understand that all this must seem very foreign and technical for you.

Here is a link that might help : [url]http://www.sql-server-performance.com/sql_server_performance_audit10.asp[/url]

rockmoose
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2006-04-12 : 19:47:28
I like to add

SET STATISTICS IO ON
SET STATISTICS TIME ON

in the Query Analyzer session that I am using. THis will show you timing and IO stats that are very important in determining what to tune. You should also enable the execution plan (as rm stated) so you can see what decisions the optimizer is making to return your data.



-ec
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2006-04-12 : 20:45:29
Duration can be "variable" for any given query depending on if the data is cached or not. Look at the reads column. Each read is 8Kb. So, take the number of reads for a column, times it by 8Kb, and that's how much IO it takes to perform said query. Once you have that number, pick the largest query and optimize it. There are lots of articles at sql-server-performance.com for such, or you can post here for help on a specific query.

With SQL Server, how much Disk IO you are doing is one of the most important measurements. The less IO you do to get a result, the faster you get the result.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-13 : 11:57:44
quote:
Originally posted by rockmoose

The rows with more than 50,000ms are likely to cause timeouts in the application resulting in whiteouts.
The rows with more than 5,000ms will certainly make the application seem slow as they have to wait for more than 5 seconds just to get the answer from the database.

I hope you have the TextData column in you trace as well.
(I did mention the Duration column earlier in the thred, and I apologize if I forgot to explicitly mention the TextData column)
Anyway, the TextData column will show the SQL statement that was sent to Sql Server.
Look at the SQL statement's in the TextData column and see if you can find which statements are the worst performing.

Take those statements and run in query analyzer, and see if you can optimize them.
By examining the query plan, and using the index wizard.

If you are new on sql-server, I understand that all this must seem very foreign and technical for you.

Here is a link that might help : [url]http://www.sql-server-performance.com/sql_server_performance_audit10.asp[/url]

rockmoose



I ran anotger trace with TextData column. When I see the taxtdata, I found that most of the values for it is "NULL"

What does this mean?

Please help
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2006-04-13 : 16:15:41
http://www.sql-server-performance.com/sql_server_performance_audit.asp

Give that a read, esp.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Michael



<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2006-04-14 : 04:45:32
"I ran anotger trace with TextData column. When I see the taxtdata, I found that most of the values for it is "NULL" "
It is dependent on the EventClass (type of event the trace captures)
Some do not have any TextData associated with them.

If you have the EventClass column, you can see which ones don't have any TextData.

rockmoose
Go to Top of Page

Jhalmans
Microsoft SQL Server Product Team

12 Posts
Posted - 2006-04-15 : 02:24:02
Running profiler is a great start to find your performance issues. In addition you should consider running performance monitor and look at the physical disk counters avg. disk sec/read, avg. disk sec/write, avg disk sec/transfer and processor\%cpu time to see if you have any physical bottlenecks.

Looking at blocking might not be a bad idea either. You can find a script to do that here:
http://support.microsoft.com/kb/271509/en-us

Another tool you can use on the profiler output is called read80trace, that will aggregate the trace output and point to the queries that consume the most disk, cpu or time among other things.
http://support.microsoft.com/default.aspx?scid=kb;en-us;887057


This posting is provided "AS IS" with no warranties, and confers no rights
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-18 : 16:11:34
quote:
Originally posted by rockmoose

"I ran anotger trace with TextData column. When I see the taxtdata, I found that most of the values for it is "NULL" "
It is dependent on the EventClass (type of event the trace captures)
Some do not have any TextData associated with them.

If you have the EventClass column, you can see which ones don't have any TextData.

rockmoose


I have event class column. The event clasee value is 15 for all the rows where the textdata value is "NULL" what does this mean?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-18 : 16:14:13
Add this to your query: WHERE TextData IS NOT NULL

Ignore the rows that do not have TextData. These often just logins and logouts, which we don't care about here. Here is the query that I use:

SELECT TOP 100 Duration, TextDat
FROM YourTraceTable
WHERE TextData IS NOT NULL
ORDER BY Duration DESC

Tara Kizer
aka tduggan
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-24 : 15:46:22
quote:
Originally posted by tkizer

Add this to your query: WHERE TextData IS NOT NULL

Ignore the rows that do not have TextData. These often just logins and logouts, which we don't care about here. Here is the query that I use:

SELECT TOP 100 Duration, TextDat
FROM YourTraceTable
WHERE TextData IS NOT NULL
ORDER BY Duration DESC

Tara Kizer
aka tduggan


When I get all rows where textdata is not null, I get only 33 rows. What is the next thing I would be doing with this?

I read somehwhere that I should try to tun the text data thing on my query analyzer, but when I copy paste that in the query analyzer and run it, I am getting errors with all of them. Am I missing something?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-24 : 15:51:17
Once you have the result set, you need to analyze each poor performing query to see if the query needs to be re-written, indexes need to be added, database schema needs to be modified, etc...

Could you post some samples?

Tara Kizer
aka tduggan
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-24 : 16:10:59
quote:
Originally posted by tkizer

Once you have the result set, you need to analyze each poor performing query to see if the query needs to be re-written, indexes need to be added, database schema needs to be modified, etc...

Could you post some samples?

Tara Kizer
aka tduggan


Sent you a PM with my queries. Don't want them to appear here.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-24 : 16:19:40
Query Analyzer is cutting off your colunm. You need to expand the result to say 7000.

Tara Kizer
aka tduggan
Go to Top of Page

yalgaar
Starting Member

43 Posts
Posted - 2006-04-24 : 16:48:05
quote:
Originally posted by tkizer

Query Analyzer is cutting off your colunm. You need to expand the result to say 7000.

Tara Kizer
aka tduggan


When I try to run the query that takes the most duration, I get the following message:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-24 : 16:51:50
In order to help, we'll need to see what you are running. Are you in the correct database where the stored procedure exists?

Be careful when running TextData as you may get undesired results. Let's say you have a stored procedure that took 60 seconds to execute, which means it'll show up in your results. Now let's say it deletes or updates data. Now when you go to run it, you will be deleting/updating data as well which may harm your environment. So you should always view the code behind the stored procedures before just running them in Query Analyzer.

Tara Kizer
aka tduggan
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-04-24 : 16:53:20
BTW, let's hope there is a WHERE clause on this query:

SELECT TOP 2147483648 ...

That one is being done dynamically as well (I know this because of sp_executesql), which can be a major performance problem.

I'm not sure what the point of grabbing the top 2 billion rows is!

Tara Kizer
aka tduggan
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site