SELECT N'Testing Connection...'

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

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT N'Testing Connection...'

Author

Topic

redbrad0
Posting Yak Master

176 Posts

Posted - 2005-05-16 : 14:36:32

I have been running a trace trying to find query's that need to be optimized. I have two questions about this...

Question 1
After a large SQL Query is run that is built thru my ASP Pages I see the following two items listed...


Event Class            TextData                                           ApplicationName             CPU    Reads   Writes
SQL:BatchCompleted     Select N'Testing Connection...'                    SQLAgent - Alert Engine     1609     0     0
SQL:BatchCompleted     EXECUTE msdb.sbo.sp_sqlagent_get_perf_counters     SQLAgent - Alert Engine     1609     96    0

These CPU is the same as the query so does that query actually take 1609*3=4827? Can I limit this?

Question 2
When you are looking for a bad query what is the best sign, high CPU Usage, Reads, Writes or Duration?

Quality NT Web Hosting & Design

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-16 : 19:04:15

How do you judge it is bad query ?

hmmmm may be it is same as follow .... :-)

1. Might you are sending very big SQL statement from Front end application. Then Network trafic will Increases (due to big packet size)

So, use stored procs . And SPs are more reliable because execution plan is available in Proc Cache.

2. If it is taking more CPU, Logical and Physical reads then Optimize the Query and before exucute check for "Query Execution plan" and supply good Clusetred or NOn-Clustered Indexes and
eliminate loops and co-related joins.

3. Avoid getting large recordsets. Better to use TOP, TOP %.

4. Understand and use the purpose of INDEX Tuning Wizard.

With Regards
Sreenivas Reddy B

redbrad0
Posting Yak Master

176 Posts

Posted - 2005-05-16 : 21:05:32

Well the Query is not something that can be put into a SP unless it is ok to having something like...

SELECT TOP 15 Data.Data_ID, Data.Data_FeedID, Data.Data_Title, Data.Data_Link, Data.Data_Desc, Data.Data_DateCreated, WEIGHT.RANK AS SSFeedRanking FROM dbo.RSS_Feeds_Data Data INNER JOIN CONTAINSTABLE(dbo.RSS_Feeds_Data, *, 'ISABOUT (@IsAbout)') WEIGHT ON Data.Data_ID = WEIGHT.[KEY]

Because the isabout is built on the fly but I thought this was not correct at all and a SP should never be used.

Also how about the Select N'Testing Connection...' above? Is that normal? Can I make that not run to free up more resources on the server?

Quality NT Web Hosting & Design

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-17 : 04:58:59

The testing connection is expected.

-------
Moo. :)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-17 : 06:56:46

You can make dang near anything into a stored proc. Sending dynamic SQL from an ASP page != Good idea.

*need more coffee*

redbrad0
Posting Yak Master

176 Posts

Posted - 2005-05-17 : 20:18:59

Is != me not equal to? So I should send it to a Stored Procedure like this?

SELECT TOP 15 Data.Data_ID, Data.Data_FeedID, Data.Data_Title, Data.Data_Link, Data.Data_Desc, Data.Data_DateCreated, WEIGHT.RANK AS SSFeedRanking FROM dbo.RSS_Feeds_Data Data INNER JOIN CONTAINSTABLE(dbo.RSS_Feeds_Data, *, 'ISABOUT (@IsAbout)') WEIGHT ON Data.Data_ID = WEIGHT.[KEY]

Quality NT Web Hosting & Design

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources