Solved

What should i use for performance in SQL Server 2005

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query - which index being used? 2 61
Grid querry results 41 80
kill process lock Sql server 9 64
Need a SQL query that creates a header row and one or more detail rows. 7 53
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 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