server slow

Question

server slow

ebinroy

Mr or Mrs. 500

Points: 548
More actions
May 2, 2007 at 11:41 pm

#123686

Hi all,
I am having a problem ,SQL server is running very slow.This is happening some days only.For example my stored procedure ususally runs less than 2 minutes, some days will take 13minutes.I dont understand the problem.All the stored procedure having the same problem.Sorry, I am not a DBA,basically a devloper.Daily morning we are taking the DB backup and indexes already applied.DB size 10461.06 MB,RAM 4GB,CPU usage is less than 50%,This is a Cinema Database,so lot of users are accessing at same time(Web,IVR,cinema ticket counters etc).We are using SQL reports.Because of the stored procedure running slow,can not view the reports.pls advice..
please help me..If you need some more information please ask ..
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Does it happen regularly or rarely. if regularly use profiler to trace the sql performance and check for the root cause. if it occurs rarely monitor the performance of the database and the system during that period to find the fallout.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

I'd follow Sugesh' advice, but also perhaps connect with SSMS and run the same query outside of the stored procedure when it's slow and grab the execution plan. This will allow you to study it and maybe compare it to the execution when it's not slow.

In 2000, I have seen execution plans change under load for no good reason.

ebinroy Mr or Mrs. 500 Points: 548 More actions · Answer 3

hi all,

Thanks for the reply.When I run the stored procedure ,it took 10 minutes to exeute, in the mean time I run 'sp_who2 active',so I noticed that CPU time=426815, diskIO=152324 for the select statement.

Little explanation about the stored procedure.I want to get the fill rate of each hall(each shows by date).(ie group by Hall,Film).That is calculated using two user functions to calculate total seats booked for the show,total amount collected in the show.I am not using any cursors in function just Joinging 4 tables.

execution plan took 89% for the below part

--this is the SQL in the SP & I am inserting this to a table variable and then updating the rate

SELECT cin_cinema_cd, cin_max_capacity, sho_film_cd, flm_film_title, flm_date_in, COUNT(sho_film_cd) AS x,

dbo.cf_fill_rate_sum_seat (@cinema, sho_film_cd,cin_cinema_cd,@startdate,@enddate) AS y,

dbo.cf_fill_rate_sum_taking(@cinema, sho_film_cd, cin_cinema_cd,@startdate,@enddate) AS z

FROM g2_CIN, g2_show_details, g2_films

WHERE sho_location_cd = @cinema AND cin_location_cd = @cinema AND sho_cinema_cd = CIN_Cinema_CD AND sho_film_cd = flm_film_cd AND

sho_show_ops_dt BETWEEN @sdate AND @edate

GROUP BY cin_cinema_cd, cin_max_capacity, sho_film_cd, flm_film_title, flm_date_in

--updating the rate

update @result set rate = (100.0 * z ) /((COUNT(sho_film_cd) * cin_max_capacity))

so the result will be like below(dummy data)

HallNo**Max_Capacity**FilmTitle**TotalShows**ActualSeating**ActualTaking**FillRate

1 349 Mr Bean 28 361 2,205.00 3.94%

1 349 300 25 1204 3,219.00 4.25%

---------------------------------------------

2 149 Mr Bean 10 267 1,205.00 2.94%

2 149 300 29 1805 4,219.00 5.25%

pls advice what is wrong?Thanks