Performance issue got to be done in few hrs need HELP PLZ
-
Use of scalar UDF's is not a good idea either
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
-
Dave Ballantyne (6/28/2010)
Sure about that Gail ?Difference in 2005 / 2008 Perhaps ?
This profiler trace screen shot is 2005.
Doesnt Look like the update statistics has invalidated the plan.
Yes, absolutely sure on both things that I've said, no, does not differ between 2005 and 2008.
SELECT * with no predicate is not going to be invalidated, it doesn't depend on the statistics, it's a table scan no matter what the column distribution looks like. Filter on a column then trace, make sure you don't have a trivial plan.
CREATE TABLE TestingRecompiles (ID int identity primary key,
SomeColumn int,
Filler char(200)
)
CREATE INDEX idx_Testing ON TestingRecompiles (SomeColumn)
Insert into TestingRecompiles (SomeColumn)
Select TOP (50000) a.column_id FROM master.sys.columns a CROSS JOIN master.sys.columns b
DBCC FREEPROCCACHE
-- begin trace (CacheMiss, CacheInsert, CacheRemove, Recompile, StmtComplete)
SELECT * FROM TestingRecompiles WHERE SomeColumn = 21
update TOP (1) TestingRecompiles SET SomeColumn = 22 WHERE SomeColumn = 21
Update Statistics TestingRecompiles WITH FULLSCAN
SELECT * FROM TestingRecompiles WHERE SomeColumn = 21
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi @Gila I have modified my stored procedure with the code which you have given me but still it took like 13+ mins to execute the stored procedure.
I have updated all the stats related to that stored procedure but still i can that particular view is taking 1:45 sec to exec and 13min for stored procedure.
What i have to do? :crying:
-
For a reference i have executed sp_who2 on my server and below is the cpu time and disk i/p but i heard that even if i restart the server they have seen the same performance.
Suggest me something please.
-
@Gail , thanks for clarifying. didnt seem right ...
@sam-3 , the most helpful thing you could do is slowly strip the query down. Once it is at the simplest it can be and you still get a difference then post those plans. They are big plans , and will take a lot of work to narrow the exact difference. Double check table structures , indexes etc. And we are assuming that the dataload is exactly the same to.
-
If you don't mind can you please tell me what you mean by slowly strip the query down? and how to do it?
Thanks.
-
Remove pieces (joins typically, or complex Where clause predicates) of the query and see what the smallest, simplest portion that you can find that still shows the performance problem.
Can you double-check that the indexes are the same in both DBs? I thought I saw clustered index scans when looking at the execution plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
@Gail
In order to test the performance i took a backup from server 2 where everything was working very fast and i restored that in my server 1 as a different database i still see the same performance issue problem.
How come the same code works differently when i ran on both servers? what you will suggest me in order to increase the performance on server 1 which is very vital for my company.
-
sam-1083699 (6/28/2010)
How come the same code works differently when i ran on both servers?Many possible reasons. Different hardware, different load, different version of SQL, that's just 3 off the top of my head.
If this is urgent, I suggest you hire someone. We're happy to help out here, but we all post in our spare time
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ya I found that we are using SQL STANDARD edition sp2 on Server 1 and Enterprise edition sp2 on Server2 and I can a lot of data load on server 1 when compared to Server 2.
You think this causing the problem? please let me if you like to look at any external information inorder to suggest me something. I have attached the cpu time and disk i/p snapshots of both servers let me know what you think?
Thanks.
-
sam-1083699 (6/28/2010)
Ya I found that we are using SQL STANDARD edition sp2 on Server 1 and Enterprise edition sp2 on Server2 and I can a lot of data load on server 1 when compared to Server 2.So the server that's slow has a higher load (hence more chance of blocking) and a lower edition of SQL? Let me guess, lower hardware spec as well?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ya lower edition of SQL on Server 1 which is slow and Enterprise edition on Server2 which is fast and coming to system configurations:
Server 1: its a very beefy server (4 quadcore processes for a total of 16 cores running at 3.33 GHz with 10GB of RAM). The RAM is kind of limiting, but I don’t believe it is causing excessive paging (but you can run the performance counters to verify). I’ve tried to run the stored procedure after a server reboot and before any of the other systems have connected to it, and it still performed in the 10+ minute range. So I don’t think the RAM is causing the issue. What do you say?
Server 2:it’s a virtual machine running on a Hyper-V server. It’s got 8GB of RAM allocated to it with 4 processors at 2.40GHz.
-
Any suggestions please
-
Please bear in mind that we have no knowledge of your data or the relationships within it , so to be presented with massive execution plans are be able to spot where the differences (and why) is quite a task.
Remove the function calls , then the outer joins , then the inner joins one by one. With luck you may find that you will be able to tie your problems down to a single join/correlated query/function call. When you are able to do that it will make life simpler to diagnose and fix the problems you are having.
Of course we are assuming here that your beast of a box is 'correctly' configured. It would be nice to know that a benchmark tool such as SQLIO has been run on both boxes and that the results have been compared and the 'bigger' box is actually faster by the expected percentage.
-
Now cross posted
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply