Solved

What should i use for performance in SQL Server 2005

Posted on 2008-11-01
2
185 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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