Solved

What should i use for performance in SQL Server 2005

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now