The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt
Thanks, DaveYou've been told wrong.
There is nothing bad about nested subquery. They, like any coding technique, should not be used unnecesarilly, but the optimizer will incorporate them in its query plan. You can try eliminating the nested UNION subquery, but you will need to link the account and TransactionalData tables into both YTD tables. This may allow you to better benefit from indexing, but will incur twice as many table scans. So whether this ends up making your code more efficient depends heavily upon your data. You just have to try it and test for yourself.
In your code, GETDATE() will not be executed for every record. It will only be executed once at the start of the query. You WILL, however, lose any benefit of an index on Process_Date by using this syntax: WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)Use this syntax instead:WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
Other problems? Dump the "SELECT *" and enumerator your columns. "SELECT *" is sloppy programming and has not business in production code.|||Nested views are not derived tables...there's a major differences...and yes avoid netsed views...BUT
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
There is no good reason to do this|||I think Blindman may have meant to use
WHERE Process_Date > DATEadd(mm, -15, GETDATE())
instead of
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)|||OK, I see it now
FROM ( SELECT *
FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT *
FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt
First I would normalize the tables..YTD0xx tables should all be 1 table with a type of...whatever that stuff means..I'm guessing month?
Also, and I'm not sure, bu this might cause a scan: RIGHT(trans.Acnt, 5)
Why are the element size different? Did you overload 1 column with additional data instead of normalizing it into 2 columns?|||Thanks all. The design is definitely something that needs to be looked at.
I'm still not in favor of nested views. Most articles I've read about the pros and cons of nested views indicate a major con is how deeply nested they can become. While SQL Server does expand the views to their base table representation, this does create a certain amount of extra work behind the scenes that is not reported as part of the execution plan. I've also seen many times where developers find a view that comes close to meeting their needs and creates another view off of this view without taking the time to research if the first view is the base view. Several times this summer I've been asked to troubleshoot poor server performance where the CPUs are over 95% utilized. I ended up finding the source of the problem was a nested view. The execution plan revealed over 100-150 icons containing multiple table scans and involving 6-10 other views and multiple base tables all the result of one select statement off of a nested view. I know the argument can be made the real problem was a poorly designed nested view and not the use of nested views itself, however, at least in my experience, most people don't take the time needed to carefully analyze existing views before using them. I'm not even going to touch on the difficulty in troubleshooting performance problems caused by poorly designed nested views since I've typed too much already. Sorry about that.
Dave|||Dave why don't you post the ddl of the tables, sample data and expecxted results.
Read the hint sticky at the top of the board|||I think Blindman may have meant to use
WHERE Process_Date > DATEadd(mm, -15, GETDATE())
instead of
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
Yes. Copy/paste error. Thanks for catching it.
And yes, nested VIEWS are bad, if only because they serve more to obfuscate code than to clarify it.|||Unless I'm just WAY off base, the sample you posted isn't syntactically correct, so I'm not about to try to fix it until we get thing clarified. Simply reformatting what you posted, I get:SELECT
trans.Entry_Code, trans.D_C, trans.ADP_Security_#
, trans.TRID, trans.Batch_Code, trans.Last_Money
, null as Shares, Settle_date as Process_Date, null as Closing_Price
, trans.Dwnld_Date, trans.Acnt, null as Mktval
, cast(Null as varchar(20)) as Cusip_#
, ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION SELECT *
FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.AcntI guarantee you that won't work. Can you re-examine what you're doing, and cut-and-paste the actual code for us to help you fix?
-PatP|||Here's the actual code. Thanks
Insert Into actimize_Data.dbo.transactions (Transaction_Date_Time, Trans_Type_Cd, Account_Key, Local_Currency_Amount, Local_Currency_Cd, Row_Insert_Date)
SELECT Transaction_Date_Time, Trans_Type_Cd, Account_Key, Local_Currency_Amount, Local_Currency_Cd, Row_Insert_Date
FROM view_Transactions_1YR
CREATE VIEW dbo.view_Transactions_1YR
AS
SELECT Process_Date AS Transaction_Date_Time,
Transaction_Type AS Trans_Type_Cd,
RIGHT(Acnt, 5) AS Account_Key,
case WHEN last_money = 0.0
THEN Mktval * (CASE WHEN D_C = 'd' THEN - 1
ELSE 1 END)
ELSE Last_Money end as Local_Currency_Amount,
'USD' AS Local_Currency_Cd,
Dwnld_Date AS Row_Insert_Date
FROM dbo.view_TransactionsData_1YR trans
LEFT OUTER JOIN dbo.tbl_Transaction_Table
ON trans.Transaction_Type = dbo.tbl_Transaction_Table.Trans_Type_Cd
WHERE (dbo.tbl_Transaction_Table.Trans_Table = 'transactions')
CREATE VIEW dbo.view_TransactionsData_1YR
AS
SELECT transactions.Entry_Code, transactions.D_C,
transactions.ADP_Security_#,
dbo.getTransType(transactions.TRID,
transactions.Entry_Code, transactions.Batch_code,
transactions.D_C, transactions.ADP_Security_#,
transactions.Last_Money, transactions.Shares) AS Transaction_Type,
transactions.Process_Date, transactions.Shares,
transactions.Closing_Price, transactions.Dwnld_Date,
transactions.Acnt, transactions.Last_Money,
transactions.Mktval, transactions.Cusip_#, transactions.TRID
FROM (Select * from view_WBTransactions_DataIn_1YR
UNION
Select * from view_WBMargin_DataIn_1YR) transactions
WHERE (isnull(transactions.Entry_Code,'') NOT IN ('AIM', 'ALL', 'CEF', 'FED', 'FND', 'MMR', 'PPS', 'REI', 'WBR'))
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'NW3' ANd Batch_code ='3N')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'CON' ANd Batch_code ='RG')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'JNL' ANd Batch_code ='MF')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'CHK' ANd Batch_code ='MN')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'ADJ' ANd Batch_code ='AS')
And ( Batch_code <> 'RG')
And ( Batch_code <> 'OT')
CREATE TABLE [tbl_Transaction_Table] (
[Trans_Type_Cd] [varchar] (50) NOT NULL ,
[Trans_Table] [varchar] (50) NULL ,
CONSTRAINT [PK_tbl_Transaction_Table] PRIMARY KEY CLUSTERED
([Trans_Type_Cd]
) )
CREATE VIEW dbo.view_WBTransactions_DataIn_1YR
AS
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_#, trans.TRID,
trans.Batch_code, trans.Last_Money, trans.Shares,
trans.Process_Date, trans.Closing_Price, trans.Dwnld_Date,
trans.Acnt, trans.Mktval, trans.Cusip_#, ACTIMIZE_DATA.dbo.account.account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACTIMIZE_DATA.dbo.account
ON ACTIMIZE_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt
CREATE VIEW dbo.view_WBMargin_DataIn_1YR
AS
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# , trans.TRID,
trans.Batch_Code, trans.Last_Money, null as Shares,
Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACTIMIZE_DATA.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBMRGN.dbo.YTD05M
WHERE (DATEDIFF(mm, Dwnld_Date, GETDATE()) <= 14)
UNION
SELECT * FROM ADPDBMRGN.dbo.YTD06M) trans
INNER JOIN ACTIMIZE_DATA.dbo.account
ON ACTIMIZE_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN dbo.tbl_Accounts_TransactionalData
ON trans.Acnt = dbo.tbl_Accounts_TransactionalData.Acnt
CREATE TABLE [YTD05B] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Process_Date] [datetime] NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[Shares] [float] NULL ,
[Security_Desc] [varchar] (30) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Cusip_#] [varchar] (9) NULL ,
[Last_Money] [float] NULL ,
[Closing_Price] [float] NULL ,
[Mktval] [float] NULL ,
[Dividend_Factor] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[Bond_Mat_Date] [datetime] NULL ,
[Bond_Int_Rate] [float] NULL ,
[Bond_Book_Value] [float] NULL ,
[Frac_Qty] [float] NULL ,
[Price_Multipler] [varchar] (1) NULL ,
[Option_Ind] [varchar] (1) NULL ,
[MSD_Code_1] [varchar] (1) NULL ,
[MSD_Code_25] [varchar] (4) NULL ,
[MSD_Code_67] [varchar] (2) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[AdvTC] [varchar] (2) NULL ,
[AdvST] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL ,
[LedgerD] [varchar] (6) NULL
)
CREATE TABLE [YTD06B] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Process_Date] [datetime] NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[Shares] [float] NULL ,
[Security_Desc] [varchar] (30) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Cusip_#] [varchar] (9) NULL ,
[Last_Money] [float] NULL ,
[Closing_Price] [float] NULL ,
[Mktval] [float] NULL ,
[Dividend_Factor] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[Bond_Mat_Date] [datetime] NULL ,
[Bond_Int_Rate] [float] NULL ,
[Bond_Book_Value] [float] NULL ,
[Frac_Qty] [float] NULL ,
[Price_Multipler] [varchar] (1) NULL ,
[Option_Ind] [varchar] (1) NULL ,
[MSD_Code_1] [varchar] (1) NULL ,
[MSD_Code_25] [varchar] (4) NULL ,
[MSD_Code_67] [varchar] (2) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[AdvTC] [varchar] (2) NULL ,
[AdvST] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL ,
[LedgerD] [varchar] (6) NULL
)
CREATE TABLE [account] (
[account_key] [varchar] (50) NOT NULL ,
[organization_key] [varchar] (50) NULL ,
[branch_key] [varchar] (50) NULL ,
[primary_representative_key] [varchar] (50) NULL ,
[split_key] [varchar] (50) NULL ,
[acct_num] [varchar] (50) NULL ,
[acct_first_name] [varchar] (50) NULL ,
[acct_middle_name] [varchar] (50) NULL ,
[acct_last_name] [varchar] (50) NULL ,
[acct_maiden_name] [varchar] (50) NULL ,
[acct_other_name] [varchar] (50) NULL ,
[acct_type_cd] [varchar] (50) NULL ,
[acct_classification_cd] [varchar] (50) NULL CONSTRAINT [DF_account_acct_classification_cd] DEFAULT ('N/A'),
[primary_party_key] [varchar] (50) NULL ,
[acct_open_date] [datetime] NULL ,
[acct_close_date] [datetime] NULL ,
[last_review_date] [datetime] NULL ,
[sector_cd] [varchar] (50) NULL ,
[investment_objective_cd] [varchar] (50) NULL ,
[risk_tolerance_cd] [varchar] (50) NULL ,
[credit_limit_local_currrency_value] [float] NULL ,
[stated_liquid_net_worth] [float] NULL ,
[stated_total_net_worth] [float] NULL ,
[stated_account_activity] [float] NULL ,
[stated_account_value] [float] NULL ,
[is_suspect] [bit] NULL ,
[is_dvp_rvp] [bit] NULL ,
[is_active] [bit] NULL ,
[is_online_account] [bit] NULL ,
[avg_balance] [float] NULL ,
[verification_status] [varchar] (50) NULL ,
[verification_date] [datetime] NULL ,
[household_id] [varchar] (50) NULL ,
[is_proprietary] [bit] NULL ,
[is_street_side] [bit] NULL ,
[is_error_account] [bit] NULL ,
[is_institutional] [bit] NULL ,
[is_employee_account] [bit] NULL ,
[is_tax_deferred] [bit] NULL ,
[is_single_joint] [bit] NULL ,
[is_fee_based_acct] [bit] NULL ,
[is_anonymous_acct] [bit] NULL ,
[is_option_approved] [bit] NULL ,
[option_approval_level_cd] [varchar] (50) NULL ,
[is_discretionary] [bit] NULL ,
[managed_acct_type] [varchar] (50) NULL ,
[row_update_date] [datetime] NULL ,
CONSTRAINT [pk_account] PRIMARY KEY CLUSTERED
(
[account_key]
) )
CREATE TABLE [tbl_Accounts_TransactionalData] (
[Acnt] [varchar] (9) NOT NULL ,
CONSTRAINT [PK_tbl_Accounts_TransactionalData] PRIMARY KEY CLUSTERED
(
[Acnt]
))
CREATE TABLE [YTD05M] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Last_Money] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[US_Tax_Status] [varchar] (1) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[ADVtc] [varchar] (2) NULL ,
[ADVst] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL
)
CREATE TABLE [YTD06M] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Last_Money] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[US_Tax_Status] [varchar] (1) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[ADVtc] [varchar] (2) NULL ,
[ADVst] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6)
)
Showing posts with label complex. Show all posts
Showing posts with label complex. Show all posts
Wednesday, March 28, 2012
Poor Performance - Identical DBs But Different Performance
Hello Everyone,
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with a
particular query. It would take approximately 22 seconds to return 100
rows, thats about 0.22 seconds per row. Note: I ran the query in single
user mode. So I tested the query on the Development server by taking a
backup (.dmp) of the database and moving it onto the dev server. I ran
the same query and found that it ran in less than a second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is related
to some external hardware issue like: disk space, memory etc. Or could
it be OS software related issues, like service packs, SQL Server
configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system
related issue.
Any Ideas would help me greatly!
Thanks,
Brian T
*** Sent via Developersdex http://www.developersdex.com ***Brian Tabios wrote:
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
> *** Sent via Developersdex http://www.developersdex.com ***
Is SQL configured the same way on both servers, same memory settings,
parallelism, etc.?
Does the query do a lot of sorting? How is TEMPDB configured between
the two machines?
Fire up Perfmon on the production machine and look at CPU, Avg. Disk
Queue Length when the query is running. Compare that to the development
machine.|||Hi Brian
Have you defragmented the indexes and updated statistics on the slower box?
When you defragmented the disc was SQL server running or not?
Have you checked sp_configure and sp_dboption values?
How did you time/execute the queries?
John
"Brian Tabios" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with a
> particular query. It would take approximately 22 seconds to return 100
> rows, thats about 0.22 seconds per row. Note: I ran the query in single
> user mode. So I tested the query on the Development server by taking a
> backup (.dmp) of the database and moving it onto the dev server. I ran
> the same query and found that it ran in less than a second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is related
> to some external hardware issue like: disk space, memory etc. Or could
> it be OS software related issues, like service packs, SQL Server
> configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Brain,
I had similar problem in the past. It turned out that one of my DBA's was
shrinking the tempdb and also he left it on autogrow @. 1mb. The problem was
solved by configuring initial bigger TEMPDB and ofcourse, shrink job was
disabled.
--
Venkat
sql server admirer
"John Bell" wrote:
> Hi Brian
> Have you defragmented the indexes and updated statistics on the slower box?
> When you defragmented the disc was SQL server running or not?
> Have you checked sp_configure and sp_dboption values?
> How did you time/execute the queries?
> John
> "Brian Tabios" wrote:
> > Hello Everyone,
> >
> > I have a very complex performance issue with our production database.
> > Here's the scenario. We have a production webserver server and a
> > development web server. Both are running SQL Server 2000.
> >
> > I encounted various performance issues with the production server with a
> > particular query. It would take approximately 22 seconds to return 100
> > rows, thats about 0.22 seconds per row. Note: I ran the query in single
> > user mode. So I tested the query on the Development server by taking a
> > backup (.dmp) of the database and moving it onto the dev server. I ran
> > the same query and found that it ran in less than a second.
> >
> > I took a look at the query execution plan and I found that they we're
> > the exact same in both cases.
> >
> > Then I took a look at the various index's, and again I found no
> > differences in the table indices.
> >
> > If both databases are identical, I'm assumeing that the issue is related
> > to some external hardware issue like: disk space, memory etc. Or could
> > it be OS software related issues, like service packs, SQL Server
> > configuations etc.
> >
> > Here's what I've done to rule out some obvious hardware issues on the
> > prod server:
> > 1. Moved all extraneous files to a secondary harddrive to free up space
> > on the primary harddrive. There is 55gb's of free space on the disk.
> > 2. Applied SQL Server SP4 service packs
> > 3. Defragmented the primary harddrive
> > 4. Applied all Windows Server 2003 updates
> >
> >
> > Here is the prod servers system specs:
> > 2x Intel Xeon 2.67GHZ
> > Total Physical Memory 2GB, Available Physical Memory 815MB
> > Windows Server 2003 SE /w SP1
> >
> > Here is the dev serers system specs:
> > 2x Intel Xeon 2.80GHz
> > 2GB DDR2-SDRAM
> > Windows Server 2003 SE /w SP1
> >
> > I'm not sure what else to do, the query performance is an order of
> > magnitude difference and I can't explain it. To me its is a hardware or
> > operating system
> >
> > related issue.
> >
> > Any Ideas would help me greatly!
> >
> > Thanks,
> > Brian T
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> >
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with a
particular query. It would take approximately 22 seconds to return 100
rows, thats about 0.22 seconds per row. Note: I ran the query in single
user mode. So I tested the query on the Development server by taking a
backup (.dmp) of the database and moving it onto the dev server. I ran
the same query and found that it ran in less than a second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is related
to some external hardware issue like: disk space, memory etc. Or could
it be OS software related issues, like service packs, SQL Server
configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system
related issue.
Any Ideas would help me greatly!
Thanks,
Brian T
*** Sent via Developersdex http://www.developersdex.com ***Brian Tabios wrote:
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
> *** Sent via Developersdex http://www.developersdex.com ***
Is SQL configured the same way on both servers, same memory settings,
parallelism, etc.?
Does the query do a lot of sorting? How is TEMPDB configured between
the two machines?
Fire up Perfmon on the production machine and look at CPU, Avg. Disk
Queue Length when the query is running. Compare that to the development
machine.|||Hi Brian
Have you defragmented the indexes and updated statistics on the slower box?
When you defragmented the disc was SQL server running or not?
Have you checked sp_configure and sp_dboption values?
How did you time/execute the queries?
John
"Brian Tabios" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with a
> particular query. It would take approximately 22 seconds to return 100
> rows, thats about 0.22 seconds per row. Note: I ran the query in single
> user mode. So I tested the query on the Development server by taking a
> backup (.dmp) of the database and moving it onto the dev server. I ran
> the same query and found that it ran in less than a second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is related
> to some external hardware issue like: disk space, memory etc. Or could
> it be OS software related issues, like service packs, SQL Server
> configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Brain,
I had similar problem in the past. It turned out that one of my DBA's was
shrinking the tempdb and also he left it on autogrow @. 1mb. The problem was
solved by configuring initial bigger TEMPDB and ofcourse, shrink job was
disabled.
--
Venkat
sql server admirer
"John Bell" wrote:
> Hi Brian
> Have you defragmented the indexes and updated statistics on the slower box?
> When you defragmented the disc was SQL server running or not?
> Have you checked sp_configure and sp_dboption values?
> How did you time/execute the queries?
> John
> "Brian Tabios" wrote:
> > Hello Everyone,
> >
> > I have a very complex performance issue with our production database.
> > Here's the scenario. We have a production webserver server and a
> > development web server. Both are running SQL Server 2000.
> >
> > I encounted various performance issues with the production server with a
> > particular query. It would take approximately 22 seconds to return 100
> > rows, thats about 0.22 seconds per row. Note: I ran the query in single
> > user mode. So I tested the query on the Development server by taking a
> > backup (.dmp) of the database and moving it onto the dev server. I ran
> > the same query and found that it ran in less than a second.
> >
> > I took a look at the query execution plan and I found that they we're
> > the exact same in both cases.
> >
> > Then I took a look at the various index's, and again I found no
> > differences in the table indices.
> >
> > If both databases are identical, I'm assumeing that the issue is related
> > to some external hardware issue like: disk space, memory etc. Or could
> > it be OS software related issues, like service packs, SQL Server
> > configuations etc.
> >
> > Here's what I've done to rule out some obvious hardware issues on the
> > prod server:
> > 1. Moved all extraneous files to a secondary harddrive to free up space
> > on the primary harddrive. There is 55gb's of free space on the disk.
> > 2. Applied SQL Server SP4 service packs
> > 3. Defragmented the primary harddrive
> > 4. Applied all Windows Server 2003 updates
> >
> >
> > Here is the prod servers system specs:
> > 2x Intel Xeon 2.67GHZ
> > Total Physical Memory 2GB, Available Physical Memory 815MB
> > Windows Server 2003 SE /w SP1
> >
> > Here is the dev serers system specs:
> > 2x Intel Xeon 2.80GHz
> > 2GB DDR2-SDRAM
> > Windows Server 2003 SE /w SP1
> >
> > I'm not sure what else to do, the query performance is an order of
> > magnitude difference and I can't explain it. To me its is a hardware or
> > operating system
> >
> > related issue.
> >
> > Any Ideas would help me greatly!
> >
> > Thanks,
> > Brian T
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> >
Poor Performance - Identical Databases Different Performance
Hello Everyone,
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is
related to some external hardware issue like: disk space, memory etc.
Or could it be OS software related issues, like service packs, SQL
Server configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system
related issue.
Any Ideas would help me greatly!
Thanks,
Brian TCheck the Windows logs on the prod server for any networking, etc problems.
Run profiler on both queries and see what else might be going on.
Turn statistics, etc on also and compare them.
If auto update stats on?... Check for fragmentation in the index or table
being used.
If you are using an ODBC connection, is it possible that the ODBC logging is
turned on for the product box?
Is it possible that an expensive 3-d screen saver might be running on prod?
What is the proc, etc resource usage on both machines during the test?
Check everything...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"BTabios@.gmail.com" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is
> related to some external hardware issue like: disk space, memory etc.
> Or could it be OS software related issues, like service packs, SQL
> Server configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>|||Check the Windows logs on the prod server for any networking, etc problems.
Run profiler on both queries and see what else might be going on.
Turn statistics, etc on also and compare them.
If auto update stats on?... Check for fragmentation in the index or table
being used.
If you are using an ODBC connection, is it possible that the ODBC logging is
turned on for the product box?
Is it possible that an expensive 3-d screen saver might be running on prod?
What is the proc, etc resource usage on both machines during the test?
Check everything...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"BTabios@.gmail.com" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is
> related to some external hardware issue like: disk space, memory etc.
> Or could it be OS software related issues, like service packs, SQL
> Server configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is
related to some external hardware issue like: disk space, memory etc.
Or could it be OS software related issues, like service packs, SQL
Server configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system
related issue.
Any Ideas would help me greatly!
Thanks,
Brian TCheck the Windows logs on the prod server for any networking, etc problems.
Run profiler on both queries and see what else might be going on.
Turn statistics, etc on also and compare them.
If auto update stats on?... Check for fragmentation in the index or table
being used.
If you are using an ODBC connection, is it possible that the ODBC logging is
turned on for the product box?
Is it possible that an expensive 3-d screen saver might be running on prod?
What is the proc, etc resource usage on both machines during the test?
Check everything...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"BTabios@.gmail.com" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is
> related to some external hardware issue like: disk space, memory etc.
> Or could it be OS software related issues, like service packs, SQL
> Server configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>|||Check the Windows logs on the prod server for any networking, etc problems.
Run profiler on both queries and see what else might be going on.
Turn statistics, etc on also and compare them.
If auto update stats on?... Check for fragmentation in the index or table
being used.
If you are using an ODBC connection, is it possible that the ODBC logging is
turned on for the product box?
Is it possible that an expensive 3-d screen saver might be running on prod?
What is the proc, etc resource usage on both machines during the test?
Check everything...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"BTabios@.gmail.com" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is
> related to some external hardware issue like: disk space, memory etc.
> Or could it be OS software related issues, like service packs, SQL
> Server configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
Poor Performance - Identical Databases Different Performance
Hello Everyone,
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is
related to some external hardware issue like: disk space, memory etc.
Or could it be OS software related issues, like service packs, SQL
Server configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system
related issue.
Any Ideas would help me greatly!
Thanks,
Brian TCheck the Windows logs on the prod server for any networking, etc problems.
Run profiler on both queries and see what else might be going on.
Turn statistics, etc on also and compare them.
If auto update stats on?... Check for fragmentation in the index or table
being used.
If you are using an ODBC connection, is it possible that the ODBC logging is
turned on for the product box?
Is it possible that an expensive 3-d screen saver might be running on prod?
What is the proc, etc resource usage on both machines during the test?
Check everything...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"BTabios@.gmail.com" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is
> related to some external hardware issue like: disk space, memory etc.
> Or could it be OS software related issues, like service packs, SQL
> Server configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>sql
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is
related to some external hardware issue like: disk space, memory etc.
Or could it be OS software related issues, like service packs, SQL
Server configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system
related issue.
Any Ideas would help me greatly!
Thanks,
Brian TCheck the Windows logs on the prod server for any networking, etc problems.
Run profiler on both queries and see what else might be going on.
Turn statistics, etc on also and compare them.
If auto update stats on?... Check for fragmentation in the index or table
being used.
If you are using an ODBC connection, is it possible that the ODBC logging is
turned on for the product box?
Is it possible that an expensive 3-d screen saver might be running on prod?
What is the proc, etc resource usage on both machines during the test?
Check everything...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"BTabios@.gmail.com" wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with
> a particular query. It would take approximately 22 seconds to return
> 100 rows, thats about 0.22 seconds per row. Note: I ran the query in
> single user mode. So I tested the query on the Development server by
> taking a backup (.dmp) of the database and moving it onto the dev
> server. I ran the same query and found that it ran in less than a
> second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is
> related to some external hardware issue like: disk space, memory etc.
> Or could it be OS software related issues, like service packs, SQL
> Server configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system
> related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>sql
Monday, March 26, 2012
Poll - Stored Procedure vs. Command Text?
Has anyone found a clear benefit to one over the other for complex SQL
in Reporting Services reports?
I've stayed away from stored procedures so that my report development
lifecycle can remain mostly independent from my team's database
migrations, but if there is a clear benefit, I will strongly consider
it.
Thanks!
Mike"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in
news:1123614436.830410.247520@.g14g2000cwa.googlegroups.com:
> Has anyone found a clear benefit to one over the other for complex SQL
> in Reporting Services reports?
> I've stayed away from stored procedures so that my report development
> lifecycle can remain mostly independent from my team's database
> migrations, but if there is a clear benefit, I will strongly consider
> it.
> Thanks!
> Mike
>
I've used both. I prefer to use queries. I'll only used stored procs if 1)
I'll be reusing the same query in a number of reports, and I want to
simplify my life for maintenance, or 2) I need to do some processing on
values prior to returning data.
I query data from 3rd party software and I hate to place stored procs in
those databases. It's too easy to forget them, have have the software
company blow them away during an update.
in Reporting Services reports?
I've stayed away from stored procedures so that my report development
lifecycle can remain mostly independent from my team's database
migrations, but if there is a clear benefit, I will strongly consider
it.
Thanks!
Mike"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in
news:1123614436.830410.247520@.g14g2000cwa.googlegroups.com:
> Has anyone found a clear benefit to one over the other for complex SQL
> in Reporting Services reports?
> I've stayed away from stored procedures so that my report development
> lifecycle can remain mostly independent from my team's database
> migrations, but if there is a clear benefit, I will strongly consider
> it.
> Thanks!
> Mike
>
I've used both. I prefer to use queries. I'll only used stored procs if 1)
I'll be reusing the same query in a number of reports, and I want to
simplify my life for maintenance, or 2) I need to do some processing on
values prior to returning data.
I query data from 3rd party software and I hate to place stored procs in
those databases. It's too easy to forget them, have have the software
company blow them away during an update.
Subscribe to:
Posts (Atom)