| Author |
Topic |
|
leoiser
Starting Member
20 Posts |
 Posted - 2007-05-03 : 02:26:37
|
| 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. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-05-03 : 05:58:40
|
| You need to run PROFILER and trace some performance data.Search here for recommendations from others on how to approach this issue....Kristen, TKizer have blogs/posts on this. |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-03 : 07:14:27
|
| find someone who can optimize your indexes. you do have them, right?have a job that reindexes your tables and updates statistics during downtime.rewriting queries for better performance would be good too. <- this is very generaldo you have proper hardware? enough memory, good disks (not raid 5) etc...?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-05-03 : 07:20:24
|
| hi, Is the problem with index? Because 2 or 3 days in a month only getting this slow down,remaining days it is faster.pls advice.Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-03 : 07:43:01
|
| please understand that just saying "It's getting slow" gives us nothing to work with.you should provide more info.what happens on those 3 days that it gets slower?if everything slows down then it's a server problem.fire up performance monitor and see what is happening._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-03 : 08:59:58
|
| May have blocking issue, run 'sp_who2 active' to double check when it happens again. |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-05-04 : 01:00:27
|
| hi all, Thanks for your 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.--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_filmsWHERE 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 @edateGROUP 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**FillRate1 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-04 : 01:56:09
|
| Can you post the DDL (Create Table plus any PKs FKs Indexes etc.) for these tables:g2_CIN, g2_show_details, g2_films(You can generate the SQL for that using SQL Server's developer tools)But that might enable us to tell you how to improve the efficiency of your query, it won't tell us what goes bad on the days everything runs slowly.Do you have database maintenance which rebuilds indexes and updates statistics? How often does that run?It might be that on "slow" days the database is in need of maintenance, and then that task runs and everything speeds up - in which case you could perhaps run that more often.Really it needs someone who knows how to use Profiler and analyse the bottle necks.If you are in a position to provide remote access to your server that is a service I could do for you (send me a private message if you would like to discuss)Kristen |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-05-04 : 04:52:07
|
| Kristen,These are my answers. Do you have database maintenance which rebuilds indexes and updates statistics? ANS :Sorry, we dont have any maintenance plan.This DB having 7 years data.Actually there are few more table(can say important using in the functions ) other than g2_CIN, g2_show_details, g2_films.These are Ticket,Ticket_booking etc.(have 6091283,3083093 records)1.Can I use "Execute DBCC DBREINDEX" for recreate index?2.What I ve to do to updates statistics?I think it is already applied.When I explore "statistics" folder under table, I can see few.What is advantage & disadvantage using this? 3.How can I send private message, can not use email poster facility in this site.Thanks for your reply... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-04 : 07:09:35
|
| 1. Yes, but it is likely to:a) lock access to users - so run during scheduled downtime, or when site is quietb) create large logging activity. If your database is in Full recovery model then make frequent TLog backups - e.g. every 10 minutes.2. You should be able to just runEXEC sp_updatestatsand that will rebuild all statistics for the current database. it uses some default setting which may not be ideal, but its better than no maintenance plan at all!3. Click on my name next to this post. Press the "Click to send an EMail" link, and you should be able to send a message to me directly.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-04 : 09:28:43
|
| Statistics will be updated when you rebuild indexes. |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-05-07 : 06:28:31
|
| hi All, while using Execute DBCC DBREINDEX what should be the recommanded fillfactor.I am having few tables which will update regulary & have more than 6091283 records.so what should be fillfactor I ve to apply these table.Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-07 : 08:46:41
|
| Fill factor of 100% is good if new keys are only added at the end (e.g. an index on an Identity column).We use 90% for indexes that have Random keys, and 80%, or less, for indexes that have VERY high rates of insertion of Random keys, but we only use <90% for a very few indexesKristen |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-05-08 : 01:20:04
|
| hi, I applied fillfacter 80% for all tables.and restarted the PC.Now the stored procedure are running faster.Let me check for few days,lets hope for the best.If I apply the Reindexing for every 2 weeks(scheduled at 4AM when server is free),it should not affect anything.pls adviceThanks for the reply. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 02:09:06
|
| "I applied fillfacter 80% for all tables"You want 100% for any table that has a clustered index (which is the default for a Primary Key) on an IDENTITY column."Reindexing for every 2 weeks"Why not do it more often? If you have a table with lots of inserts then 2 weeks is probably going to be too long. (We do it every night, but only on tables that are fragmented). Still, better than nothing!Kristen |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-05-09 : 00:22:51
|
| hi kristen, Thanks again.if I ve a table having columns SHOW_ID,CINEMA_CD,LOCATION_CD,FILM_CD etc , In that SHOW_ID is the identity column.But the primary key are (CINEMA_CD,LOCATION_CD,FILM_CD).so in this case what should be recommended fill factor?with regards,leo |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 08:52:45
|
| If you have an index on SHOW_ID make it 100%The PK index on CINEMA_CD, LOCATION_CD, FILM_CD should allow some "slack" for insertion of new records. Only you can gauge how much that should be! but it will depend in part on how many inserts there are relative to reads, and also relative to the frequency of Rebuilds (which will gain-back the original amount of free space for more inserts to then use up again)Kristen |
|
|
|
|
SQL server 2005 running slow