?
Solved

What should i use for performance in SQL Server 2005

Posted on 2008-11-01
2
Medium Priority
?
196 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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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