Extremly bad performance Stored Procedures

The last few days my sqlserver executes stored procedures badly.
When I run the Query Analyzer to execute a certain stored procedure it takes more than 2 minutes to execute the procedure. When I copy the contents of de SP to Query analyser to run it as an sql statement it find's the results within a second.
This behavior dissapear's after a while and comes back randomly.
I had the problem Friday afternoon then tuesday and now again.
Between these day's my sqlserver works fine.
Can anybody please help me with this problem.Have you tried using the "with recompile" option ? Your query plan is probably based on an outdated data distribution or schema. Running the "with recompile" option will regenerate the query plan. Also, are you parameters to the stored procedure vary enough that the execution plans change ? Do a comparison in query analyzer - using show execution plan.|||I was able to elimante the problem by altering de SP.
In the SP there where more than 4 joins to the same table.
When I made a user defined function and replaced those joins with this function, it all works fine.

But one question remains. How is it possible that the query analyser didn't have problems with the joins but de SP did have?|||Did you try the recompile ? Sometimes, if your table(s) involved in the query change enough - the query plan needs to change as well. When you run it in query analyzer, the query plan is generated dynamically. For the sp, it could still be using the original query plan when you created it. That is why I suggested to run the 2 in query analyzer with the "show execution plan".

eXtremely Long Time in Execution Query

Hi all,

I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?

Thanks,

Ning

Reminds me of some hotfix descriptions - Have you checked knowledgebase articles for "slow query"? Here's an example that relates to fast forward-only queries:

"FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005": http://support.microsoft.com/default.aspx/kb/926024

|||

I found the reason, but not sure why, either do I have a way to fix that.

In one of my select sentence, I have a field, nvarchar(2000), if I exclude that field in the select statement then everything is back to speed. Otherwise, we are cralwing like 1000 lines per minute, so 1 million lines will be 1000 minute ... (without that field we are at 100,000 per minute). I am not sure why a field can cause such a big deal 100 times in performance diff ...

Microsoft has to explain this ...

Thanks,

Ning

|||

I am wondering if it is joining to the table containing this field early in the query and so having to carry the up to 2K a row of data round (for a million rows - that's up to 2G). This would probably show as an explosion in the size of tempdb (as this is where it is likely to be caching this data during the query).

Was it just the field or the table providing the field which you removed from the query. If it was the table you might try putting it at the bottom of the from clause and using the FORCE ORDER Query hint. However this will prevent the query planner rearranging any of the tables so you might want to put them in the order specified by the plan for the fast query without that field in.

Extracting data to CSV file

How do you write a query in Query Analyzer that extracts data from a table
into a .CSV file. I need the data in that format in order to upload into an
old Sybase database. ThanksEither use BCP, or select the Query / Results to File option in QA, or
use DTS, or use OSQL with the output switch option. In principle you
can do these things from a script in QA but usually that's not
necessary. If you want to automate it then use SQL Agent or some other
process to invoke one of those methods.
David Portas
SQL Server MVP
--|||SELECT ColA + ',' + ColB + ',' + ColC FROM TableName
Or
Use DTS to export the table to a CSV file
Or
Use DTS to export the file directly to Sybase.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisB" wrote:

> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into a
n
> old Sybase database. Thanks|||Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SMyServ -Uuser -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SMyServ -Uuser -Ppass'
"ChrisB" <ChrisB@.discussions.microsoft.com> wrote in message
news:923AF22E-DF46-4DDB-84BE-18FCAEBE09FF@.microsoft.com...
> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into
an
> old Sybase database. Thanks|||The query requires nothing special. Just execute the query within Query
Analyzer with the results going to the grid. When you get the results back,
click the mouse in the grid and choose the menu option File.. Save As..
"ChrisB" <ChrisB@.discussions.microsoft.com> wrote in message
news:923AF22E-DF46-4DDB-84BE-18FCAEBE09FF@.microsoft.com...
> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into
> an
> old Sybase database. Thanks