April 23, 2012 at 1:27 am
Good day to all!
I'm new to SQL server environment...
right now, we are using it to our client.
I would like to check if the server is in the optimize settings or utilizing its cpu cores (8 cores) and memory capacity (8 gig).
Scenario is that our web apps (asp.net) is having performance problem.
Single user on a particular module will have an estimation of 4-5 sec data retrieval (stored procedure was used to retrieve data). row count of the table is 600,000+ records (optimized with indexes).
When we add 2 or more users, data retrieval estimated to 25-30 secs.
What should i do to optimize and maximize its performance?
Server specs:
CPU: Quad Core(2 cpu, 8 core)
Memory: 8gig
OS: Windows 2008 (32bit)
SQL Server: 2005 Standard Edition
April 24, 2012 at 9:08 am
You still have a number of things to think about.
1. If SQL is set to 32 bit it can only access up to 4GB of memory and has to share the 4GB of memory with the OS and application. To get around this the AWE setting and Lock Pages in Memory will need to be set. http://support.microsoft.com/kb/811891
2. You may also need to set the PAE flag in the boot.ini file.
3. Do you have any re-indexing maintenance jobs created/running on your system? Check out the article: http://www.sqlservercentral.com/Forums/Topic150354-5-1.aspx
4. Have you evaluated if additional new indexes need to be created? How much do you understand about indexes needs to be determined first. The link to the video below is very informative but assumes you have a basic understanding of performance analysis and indexes to begin with. Before doing anything with this in a production environment - always try first on your test system and always ensure you have made backups!
Index Analysis: http://technet.microsoft.com/en-us/sqlserver/gg545020.aspx
About This Video: Overview of index analysis techniques, validating if your indexes are using the right combination of keys, if your indexes are being used and how, if there are missing indexes and if your indexes are still healthy and most optimal.
April 24, 2012 at 9:14 am
Performance has less to do with server settings and more to do with properly written code and good indexes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 9:18 am
Single user on a particular module will have an estimation of 4-5 sec data retrieval (stored procedure was used to retrieve data). row count of the table is 600,000+ records (optimized with indexes).
i would seriously look at the locking involved - if your query is taking 5 seconds and then another user is queuing up for that data it would explain it.
can you post the procedure?
MVDBA
April 24, 2012 at 9:47 pm
Thank you tom.lemmer, GilaMonster, Michael Vessey for the response...
Correction on the specs i provided last time (just receive the full info from the client)
*Quad-Core Xeon E5420 (2x6 MB cache) 2.50 Ghz
*4X4 GB DDR2 667Mhz Memory
*Windows 2003 Enterprise Edition-32bit R2
*SQL Server 2005 Standard Edition
@tom.lemmer, i've check the system properties of the server and found that 16GB were found. In the boot.ini, PAE is not enable (how will i know if they are using Hot Add Memory or DEP is enable) but the system found it 16GB. Ill take a study on the re-indexing maintenance job (i haven't tried it and i check the server if they have a plan guide but nothing is there).
this is the table structure for the said module
CREATE TABLE [dbo].[DRSummTemp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DRID] [int] NULL,
[StyleNo] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [int] NULL,
[UnitPrice] [money] NULL,
[Amount] [money] NULL,
[Cost] [money] NULL,
CONSTRAINT [PK_DRSummTemp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_DRSummTemp] UNIQUE NONCLUSTERED
(
[DRID] ASC,
[StyleNo] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
@gilamonster, ill study that article, thanks
@michael-2 Vessey, this is the stored procedure for retrieval
ALTER PROCEDURE [dbo].[Get_DRTotalQuantityAndCost_SP]
-- Add the parameters for the stored procedure here
@CustomerNumber bigint,@DateFrom DateTime = Null, @DateTo DateTime = Null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select sum(DRSummTemp.Quantity) As TOTAL_QUANTITY,sum((DRSummTemp.Quantity*DRSummTemp.cost)) as TOTAL_COST
from DRSummTemp where DRSummTemp.drid in
(select ID from DRTemp where DRDATE between @DateFrom and @DateTo and CUSTNO = @CustomerNumber);
END
April 25, 2012 at 1:28 am
Post the definition of DRTemp please.
Avoid plan guides unless you really, really know what you're doing and why you're using them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 1:44 am
CREATE TABLE [dbo].[DRTemp](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DRNo] [nvarchar](15) NOT NULL,
[DRDate] [datetime] NOT NULL,
[CustNo] [int] NOT NULL,
[TotalAmt] [money] NULL,
[TotalQty] [int] NULL,
CONSTRAINT [PK_DRTemp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DRTemp] ON [dbo].[DRTemp]
(
[ID] ASC,
[DRNo] ASC,
[CustNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
April 25, 2012 at 1:47 am
no indexes on drsumtemp then ? - you said 600k rows.... i'm guessing the correct indexes will make a difference
MVDBA
April 25, 2012 at 1:49 am
also is DRID a foreign key? - it looks like it to me - why is it nullable? is there a foreign key constraint on it? - i'd definatly look at putting an index on that colum and re-check your query plan
MVDBA
April 25, 2012 at 2:00 am
You have no indexes on DRTemp that support that query.
Add an index (CUSTNO, DRDATE) INCLUDE (ID)
This index is likely useless
CREATE NONCLUSTERED INDEX [IX_DRTemp] ON [dbo].[DRTemp]
(
[ID] ASC,
[DRNo] ASC,
[CustNo] ASC
)
It has the same leading column as the clustered index, with a wider key. Drop it. Any queries that use it can use the clustered index.
I would also suggest that you drop this:
CONSTRAINT [IX_DRSummTemp] UNIQUE NONCLUSTERED
(
[DRID] ASC,
[StyleNo] ASC
)
and replace it with
CREATE UNIQUE NONCLUSTERED INDEX IX_DRSummTemp
ON DRSummTemp
(
[DRID] ASC,
[StyleNo] ASC
)
INCLUDE (Quantity, Cost)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 2:15 am
@michael-2, sir you're right... i didn't able to put indexes in DRSummTemp table (this is just an extension table handling current records). The original table contains nulls and based on the current table schema (i'll enhanced it and make a report out of it). I'll create the index based on GilaMonster's suggestion.
@gilamonster, ill take your advice for creating proper indexes for the said tables (i'll adopt it to other tables with bulk request)
One last thing, how will i check if the current sql server utilizes 8 cores and its max memory?
is there a test script for checking?
Thank you very much for the kind response.
April 25, 2012 at 2:22 am
csarsonas (4/25/2012)
@Michael, sir you're right... i didn't able to put indexes in DRSummTemp table (this is just an extension table handling current records). The original table contains nulls and based on the current table schema (i'll enhanced it and make a report out of it). I'll create the index based on GilaMonster's suggestion.@gilamonster, ill take your advice for creating proper indexes for the said tables (i'll adopt it to other tables with bulk request)
One last thing, how will i check if the current sql server utilizes 8 cores and its max memory?
is there a test script for checking?
Thank you very much for the kind response.
the only way to see that is to put lots of traffic through your server and run perfmon.
but it won't use the max memmory until you configure PAE and AWE (you also need to ensure that 3GB switch is not in your boot.ini) - even then task manager will still only show 1.7GB of used memory , since the memory is being "swapped"
if you run sp_configure and post the results back to the forum someone might be able to check you have the correct settings
MVDBA
April 25, 2012 at 2:22 am
If you haven't restricted SQL to not use 8 cores (affinity mask), it will use 8 cores as necessary
SQL will use memory up to the max server memory setting. For 8GB, that should probably be set to 6. Also ensure /PAE is enabled if it's not a default in the OS and that AWE is enabled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 2:31 am
Got it Sir!
I'll be in the client's area to test all plans and configuration...
I'll post the result afterwards....
Thank you very very much sir!
April 26, 2012 at 1:43 am
Good day sir, i just provide the necessary settings and configuration but the improvement is around 1 to 2 secs:
About the index creation, i cant create unique nonclustered index due to duplicate in DRSummTemp table.
Here are the following tables and scripts and attachment:
USE [IRMS-DB]
GO
/****** Object: Table [dbo].[DRTemp] Script Date: 04/26/2012 15:39:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DRTemp](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DRNo] [nvarchar](15) NULL,
[DRDate] [datetime] NULL,
[CustNo] [int] NULL,
[TotalAmt] [money] NULL,
[TotalQty] [int] NULL,
CONSTRAINT [PK_DRTemp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DRTemp] ON [dbo].[DRTemp]
(
[CustNo] ASC,
[DRDate] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DRSummTemp] Script Date: 04/26/2012 15:39:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DRSummTemp](
[DRID] [int] NOT NULL,
[StyleNo] [nvarchar](50) NULL,
[Quantity] [int] NULL,
[UnitPrice] [money] NULL,
[Amount] [money] NULL,
[Cost] [money] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DRSummTemp] ON [dbo].[DRSummTemp]
(
[DRID] ASC,
[StyleNo] ASC
)
INCLUDE ( [Quantity],
[Cost]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CRSTemp] Script Date: 04/26/2012 15:39:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CRSTemp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CRSNo] [nvarchar](15) NULL,
[CRSDate] [datetime] NULL,
[CustNo] [int] NULL,
[TotalAmt] [money] NULL,
CONSTRAINT [PK_CRSTemp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CRSTemp] ON [dbo].[CRSTemp]
(
[CustNo] ASC,
[CRSDate] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CRSSummTemp] Script Date: 04/26/2012 15:39:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CRSSummTemp](
[CRSID] [int] NOT NULL,
[StyleNo] [nvarchar](50) NULL,
[Quantity] [int] NULL,
[UnitPrice] [money] NULL,
[Amount] [money] NULL,
[Cost] [money] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CRSSummTemp] ON [dbo].[CRSSummTemp]
(
[CRSID] ASC,
[StyleNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
boot.ini config:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /NoExecute=OptOut /PAE
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply