Solved

What should i use for performance in SQL Server 2005

Posted on 2008-11-01
2
193 Views
Last Modified: 2012-05-05
I have some stored procedures, which are going to fetch some details from tables. Those tables contains small no. of records, not more than 1000 records in it. But those procedures are going to execute around 3000 times in 2 hour for specific day and specific time. I have question that what should i use to get more performance, Inline table valued function or scalar functions?
0
Comment
Question by:ferik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
HoggZilla earned 125 total points
ID: 22856917
You are asking a good question but the details make all the difference. It depends on the number of scalar functions and the complexity of the SQL within the functins. I suggest your run some tests. Here is a script I have used in the past to get some timings. Also use the Database Engine Tuning Advisor to get suggestions on Indexes and Stats.
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Calculate RunTime
DECLARE @start AS datetime
DECLARE @end AS datetime
DECLARE @duration AS INT
SELECT @start = CURRENT_TIMESTAMP;
-- Place Query Here
-- =======================================================================================
-- build a while to execute many times
 
	select * from dbo.fn_getval(X.X)
 
-- ========================================================================================
SELECT @end = CURRENT_TIMESTAMP
SELECT @duration = DATEDIFF(ms,@start,@end);
PRINT 'Duration(milliseconds): ' + cast(@duration as varchar(50));
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Open in new window

0
 

Author Comment

by:ferik
ID: 22882911
I have a scalar function which is use in procedure. This procedure is going to execute approx 1500-2000 times in an hour. However this is fix, that in a week, there are only 5-6 hours when procedure exeuctes this many times, for rest time it executes in normal way.

Scalar function fetch data from one table which has around 100,000 rows and having composite primary key on 6 columns.

My prime concern is performance, i have notice, that during peak time, the CPU util goes upto 100% for several minutes. We have DELL server with 8gb of ram, tons of hdd space, quad processor and naturally no other softwares loaded except SQL server 2005.

Do I require to use scalar function or inline function for performance?

I have tried to capture execution log in profiler and run DTA on it. But almost all time it simply suggest to create various index and statistics. and most of the time due to more data it fails to analyse.

i know overall performance is on-going job :) Can you suggest something through which i can get immediate performance?
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question