October 27, 2005 at 6:53 am
Hi,
I am in serious need of help....
We currently run a SQL database on the following server:
Item Value
OS Name Microsoft Windows 2000 Server
Version 5.0.2195 Service Pack 4 Build 2195
OS Manufacturer Microsoft Corporation
System Name Name
System Manufacturer Intel
System Model S7501HG0
System Type X86-based PC
Processor x86 Family 15 Model 2 Stepping 7 GenuineIntel ~2392 Mhz
Processor x86 Family 15 Model 2 Stepping 7 GenuineIntel ~2392 Mhz
Processor x86 Family 15 Model 2 Stepping 7 GenuineIntel ~2392 Mhz
Processor x86 Family 15 Model 2 Stepping 7 GenuineIntel ~2392 Mhz
BIOS Version S7501HG0 v9.00
Windows Directory C:\WINNT
System Directory C:\WINNT\system32
Boot Device \Device\Harddisk0\Partition2
Locale United States
User Name Name
Total Physical Memory 3,931,436 KB
Available Physical Memory 1,851,496 KB
Total Virtual Memory 9,273,220 KB
Available Virtual Memory 5,263,992 KB
Page File Space 5,341,784 KB
Page File C:\pagefile.sys
The database is 20,5 GB in size.
Here is some info
Number of report queries per day 360
Number of Age Analysis queries per day 20
Number of Account Enquiries queries per day 4200
Number of Registration queries per day 107
Number of TreeView queries per day 107
Number of Transaction per day 1009964
We are busy for 9 hours of the day and 90% of the processing happens during this time.
We currently have 120 users but if you look at the amount of active users it averages about 30 users.
The performance logs AVERAGE figures are as follows:
% Processor Time (_Total) = 23%
% Processor Time (sqlserver) = 81%
Memory Cache Faults/Sec = 5
Memory Pages/Sec = 2
%Disk Time (_Total) = 19
Current Disk Que Length = 2 but this should be fine as there are 3 physical disks on server
Available RAM 1812 MB
The parallelism is set to a max of 32.
The Buffer Cache hit ratio sits at an average of 99.815
The page life expectancy sits at an average of 2150
Average Latch Wait Time in ms is 888.930
Latch waits/sec is 2154.122
Total Latch wait time ms is 180.376
SQL Compilations/sec = 3.534
SQL re-Compilations/sec = 0.111
There are 3 disks
Disk 1 RAID 5
Partitioned into 2
C: has system
D: has database
Disk 2
E: Backups
Disk 3
F:
Contains Logs and Tempdb
The problem is that the machine is dead slow when working on it via the application.
The application is Loaded on Two terminal server 2003 boxes that connect to this SQL server via a 1Gbps network.
Any ideas would be a great help.....
October 27, 2005 at 7:24 am
Have you made any updates to the application? Have you used SQL Profiler to see what SQL statements are being executed? You could look in Profiler and how long statements are taking.
October 27, 2005 at 7:32 am
I take it that the machine works fine when NOT using the application?
October 27, 2005 at 7:39 am
The box is not CPU or I/O bound. My first question before looking at the application would be what type of maintenenace is being performed outside of backups. (DBCC's, indexes rebuilds, updating statistics, recompiling objects to rebuild query plans based on stats and unfragmented and reorganized indexes. (recompiling assumes stored procedures are involved).
If you are doing every thing you can from a data server perspective I would then start looking at the application (using profiler or other tools) to identify long runninig processes that may need tuning via code changes or indexing.
October 27, 2005 at 7:51 am
We have been running profiler and modifying long running queries.
Currently most queries execute in 15seconds with odd ones at larger sizes
No changes have been made to the application which is called ICAS.
The server is also slow when connecting via MS query analyser. from a local pc.
October 27, 2005 at 7:58 am
Are you doing maintenanace? How often?
October 27, 2005 at 8:04 am
We run the std sql optimiser this is done before and after the backups.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 664D0E61-8032-4E0E-821D-56F6BF7A2A36 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "E:\OffLine\Daily\0600" -DelBkUps 1DAYS -BkExt "BAK"'
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 950E44CC-B8C5-4673-AEC2-35FA38AFA0BF -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "E:\OffLine\Daily\1800" -DelBkUps 1DAYS -BkExt "BAK"'
October 27, 2005 at 8:11 am
Is it a problem that just happened this morning?
Is the application still working or does anybody get timeout errors?
October 27, 2005 at 8:39 am
Nope been going on for a while since the begining of Sep when we centralised the Db's into one. We just not getting anywhere at the moment and need more help.
The application still works and yes we have about 15 logged events a day of timeouts. If the users log out the application and back in though they are then able to carry on.
October 27, 2005 at 8:47 am
If both those jobs above are it you may not be doing the proper maintenance to support this application.
Basic maintenenace example:
Full database backup: Daily (Recovery to point in tiime within 24hrs)
Transaction Log backups (Recovery to point in tiime from full backup)
Database Consistency checks (Check for corruption)
Rebuilding Indexes (Updates stats and Reorganizes/defragments clustered index data and secondary index pointers)
Update statistics (Builds staistics on tables, columns, and indexes for use by the optimizer)
Recompiling objects (sp_recompile on all tables which recompiles stored procedures and triggers the next time they are used. This allows query plans to be rebuilt and stored based on updated indexes and statistics)
NOTE: db_options auto update stats, auto create stats, and torn page detection accomplish DBCC's and maintaining statistics, but at an overhead to the application). In the end if you don't rebuild query plans this only help in-line sql.
October 27, 2005 at 11:18 pm
We do the two jobs as well as the following,
Full database backup. Database Consistency checks, Rebuilding Indexes, Update statistics
October 28, 2005 at 2:02 am
The database is 20,5 GB in size.
Here is some info
Number of report queries per day 360
Number of Age Analysis queries per day 20
Number of Account Enquiries queries per day 4200
Number of Registration queries per day 107
Number of TreeView queries per day 107
Number of Transaction per day 1009964
Beside Indexes, update statisics, backup or maint. jobs I want to give you one hint;
Try to figger out the difference between OLTP and OLAP (and split them on different servers)
and if 'Number of Account Enquiries queries per day 4200" is refering to DNS, buy yourself a DNS server!
October 28, 2005 at 4:39 am
'Number of Account Enquiries queries per day 4200' -- This refers to the number of physical account enquiries our staff do on accounts when people call in and ask for the balance on their accounts.
The size of an account enquiry is 606546 bytes in size.
I will look into OLTP and OLAP.
October 28, 2005 at 5:31 am
'Number of Account Enquiries queries per day 4200' -- This refers to the number of physical account enquiries our staff do on accounts when people call in and ask for the balance on their accounts.
The size of an account enquiry is 606546 bytes in size.
I will look into OLTP and OLAP.
October 28, 2005 at 7:12 am
It's mostly a locking issue. You have about 1800 trans a minute (1009964/9 working hours a day) and have some long queries. Check your transaction isolation level the query is using. If the business rule allows, change it to read uncommitted or use nolock table hint to some tables. For the tables involved in data modification/insert/delete, nolock hint can also be used to some lookup/configuration tables.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply