index and key: due

Showing posts with label due. Show all posts

Friday, March 30, 2012

Index Speed use of REPLACE in clause

I have a frequently run Query, due to the design of the Sproc the indexed
columns I have added to support these are being used but are slow due to the
fact the use of the REPLACE in the Clause (Replace is being used to remove
spaces)
I.E
Select <columns>
FROM <Table>
WHERE
(sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 = ''hambly_neil@.hotmail.com')
OR
(REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') = 'N118HQ')
Does anyone have any ideas on recoding or indexes changes to help here
--
Neil HamblyHow about cleansing the data on the way in (i.e. when you INSERT it, run the
REPLACE), so that you won't have to do it every time you query?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> I have a frequently run Query, due to the design of the Sproc the indexed
> columns I have added to support these are being used but are slow due to
the
> fact the use of the REPLACE in the Clause (Replace is being used to remove
> spaces)
> I.E
> Select <columns>
> FROM <Table>
> WHERE
> (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => ''hambly_neil@.hotmail.com')
> OR
> (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => 'N118HQ')
> Does anyone have any ideas on recoding or indexes changes to help here
> --
> Neil Hambly|||This would be the ideal scenario of course, unofrtunately due to Data-Privacy
and other reasons, the data cannot be Cleansed
One thought I had was the use of Computed colums - do not know if this would
be suitable for this type of issue
"Adam Machanic" wrote:
> How about cleansing the data on the way in (i.e. when you INSERT it, run the
> REPLACE), so that you won't have to do it every time you query?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > I have a frequently run Query, due to the design of the Sproc the indexed
> > columns I have added to support these are being used but are slow due to
> the
> > fact the use of the REPLACE in the Clause (Replace is being used to remove
> > spaces)
> > I.E
> > Select <columns>
> > FROM <Table>
> > WHERE
> > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > ''hambly_neil@.hotmail.com')
> > OR
> > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > 'N118HQ')
> >
> > Does anyone have any ideas on recoding or indexes changes to help here
> >
> > --
> > Neil Hambly
>
>|||Possibly, yes. You can index computed columns to semi-persist them, as well
(they'll be persisted in the index); in this case, you could do:
ALTER TABLE YourTable
ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
ALTER TABLE YourTable
ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
PostCodeNoSpace)
... That would solve your issue, I think. But _better_ would still be to
cleanse the data -- I'm not sure what you mean by data privacy; the data is
in your database already, isn't it?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> This would be the ideal scenario of course, unofrtunately due to
Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this
would
> be suitable for this type of issue
> "Adam Machanic" wrote:
> > How about cleansing the data on the way in (i.e. when you INSERT it, run
the
> > REPLACE), so that you won't have to do it every time you query?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > I have a frequently run Query, due to the design of the Sproc the
indexed
> > > columns I have added to support these are being used but are slow due
to
> > the
> > > fact the use of the REPLACE in the Clause (Replace is being used to
remove
> > > spaces)
> > > I.E
> > > Select <columns>
> > > FROM <Table>
> > > WHERE
> > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > ''hambly_neil@.hotmail.com')
> > > OR
> > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > 'N118HQ')
> > >
> > > Does anyone have any ideas on recoding or indexes changes to help here
> > >
> > > --
> > > Neil Hambly
> >
> >
> >|||Yes, a little test on the Northwind database suggests that this should
work (provided you are using Enterprise Edition).
Gert-Jan
Neil Hambly wrote:
> This would be the ideal scenario of course, unofrtunately due to Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this would
> be suitable for this type of issue
> "Adam Machanic" wrote:
> > How about cleansing the data on the way in (i.e. when you INSERT it, run the
> > REPLACE), so that you won't have to do it every time you query?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > I have a frequently run Query, due to the design of the Sproc the indexed
> > > columns I have added to support these are being used but are slow due to
> > the
> > > fact the use of the REPLACE in the Clause (Replace is being used to remove
> > > spaces)
> > > I.E
> > > Select <columns>
> > > FROM <Table>
> > > WHERE
> > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > ''hambly_neil@.hotmail.com')
> > > OR
> > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > 'N118HQ')
> > >
> > > Does anyone have any ideas on recoding or indexes changes to help here
> > >
> > > --
> > > Neil Hambly
> >
> >
> >|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> Yes, a little test on the Northwind database suggests that this should
> work (provided you are using Enterprise Edition).
What's Enterprise Edition have to do with it?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Re: Data privacy act - We do not own all the data some of it is supplied by
our clients.
Anyway I built some computed columns with the formula using Replace(sTel1, '
', '') etc..Created indexes on these columns and amended my Sprocs to use the
computed columns etc.. Went from 10 secs (22, 000 logical reads) to a few ms
with 12 logical reads..
As this is run over 300,000 times per month that is a huge perf impact
Thanks for your posts
"Adam Machanic" wrote:
> Possibly, yes. You can index computed columns to semi-persist them, as well
> (they'll be persisted in the index); in this case, you could do:
> ALTER TABLE YourTable
> ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
> ALTER TABLE YourTable
> ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
> CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
> PostCodeNoSpace)
>
> ... That would solve your issue, I think. But _better_ would still be to
> cleanse the data -- I'm not sure what you mean by data privacy; the data is
> in your database already, isn't it?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> > This would be the ideal scenario of course, unofrtunately due to
> Data-Privacy
> > and other reasons, the data cannot be Cleansed
> > One thought I had was the use of Computed colums - do not know if this
> would
> > be suitable for this type of issue
> >
> > "Adam Machanic" wrote:
> >
> > > How about cleansing the data on the way in (i.e. when you INSERT it, run
> the
> > > REPLACE), so that you won't have to do it every time you query?
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > > I have a frequently run Query, due to the design of the Sproc the
> indexed
> > > > columns I have added to support these are being used but are slow due
> to
> > > the
> > > > fact the use of the REPLACE in the Clause (Replace is being used to
> remove
> > > > spaces)
> > > > I.E
> > > > Select <columns>
> > > > FROM <Table>
> > > > WHERE
> > > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > > ''hambly_neil@.hotmail.com')
> > > > OR
> > > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > > 'N118HQ')
> > > >
> > > > Does anyone have any ideas on recoding or indexes changes to help here
> > > >
> > > > --
> > > > Neil Hambly
> > >
> > >
> > >
>
>|||I could be wrong. I thought that maybe indexes on computed columns would
not be automatically used in Standard Edition, just as indexes on views
are not automatically used (unless you use Enterprise Edition or provide
special hints).
But as I said: I could be wrong. Maybe someone with Standard Edition can
test this example to see if an index seek is used.
USE Northwind
GO
ALTER TABLE Customers ADD CompanyNameNoSpace AS
Replace(CompanyName,space(1),'')
GO
CREATE INDEX IX_Customers_CompanyNameNoSpace ON
Customers(CompanyNameNoSpace)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
GO
SET SHOWPLAN_TEXT OFF
Gert-Jan
Adam Machanic wrote:
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > Yes, a little test on the Northwind database suggests that this should
> > work (provided you are using Enterprise Edition).
> What's Enterprise Edition have to do with it?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --|||Confirmed:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
|--Compute
Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
Name], Convert(space(1)), Convert(''))))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Northwind].[dbo].[Customers]))
|--Index
Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
:-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C2E4BC.868B93CB@.toomuchspamalready.nl...
> I could be wrong. I thought that maybe indexes on computed columns would
> not be automatically used in Standard Edition, just as indexes on views
> are not automatically used (unless you use Enterprise Edition or provide
> special hints).
> But as I said: I could be wrong. Maybe someone with Standard Edition can
> test this example to see if an index seek is used.
> USE Northwind
> GO
> ALTER TABLE Customers ADD CompanyNameNoSpace AS
> Replace(CompanyName,space(1),'')
> GO
> CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> Customers(CompanyNameNoSpace)
> GO
> SET SHOWPLAN_TEXT ON
> GO
> SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> GO
> SET SHOWPLAN_TEXT OFF
> Gert-Jan
> Adam Machanic wrote:
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > > Yes, a little test on the Northwind database suggests that this should
> > > work (provided you are using Enterprise Edition).
> >
> > What's Enterprise Edition have to do with it?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --|||Thanks,
Gert-Jan
Adam Machanic wrote:
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> |--Compute
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C2E4BC.868B93CB@.toomuchspamalready.nl...
> > I could be wrong. I thought that maybe indexes on computed columns would
> > not be automatically used in Standard Edition, just as indexes on views
> > are not automatically used (unless you use Enterprise Edition or provide
> > special hints).
> >
> > But as I said: I could be wrong. Maybe someone with Standard Edition can
> > test this example to see if an index seek is used.
> >
> > USE Northwind
> > GO
> > ALTER TABLE Customers ADD CompanyNameNoSpace AS
> > Replace(CompanyName,space(1),'')
> > GO
> > CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> > Customers(CompanyNameNoSpace)
> > GO
> > SET SHOWPLAN_TEXT ON
> > GO
> > SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> > GO
> > SET SHOWPLAN_TEXT OFF
> >
> > Gert-Jan
> >
> > Adam Machanic wrote:
> > >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > > > Yes, a little test on the Northwind database suggests that this should
> > > > work (provided you are using Enterprise Edition).
> > >
> > > What's Enterprise Edition have to do with it?
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --|||Actually, I just took a closer look at the execution plan. Interesting that
CompanyNameNoSpace is being re-computed after the bookmark lookup, even
though it was used for the initial index seek! I'm surprised that SQL
Server can't use the value from the index...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ekLNvJE5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
> |--Compute
>
Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
>
Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
>|||Indeed. I hadn't noticed.
I think Microsoft never took the time to build that optimization. To me,
that shows that they really only added a rudimentary version of indexed
computed columns.
Because the index value could have been used. If you change the query to
SELECT CustomerID,CompanyNameNoSpace FROM Customers WHERE
CompanyNameNoSpace='EasternConnection'
you will see that only the covering index is accessed, and that the
computed value is not recomputed.
Gert-Jan
Adam Machanic wrote:
> Actually, I just took a closer look at the execution plan. Interesting that
> CompanyNameNoSpace is being re-computed after the bookmark lookup, even
> though it was used for the initial index seek! I'm surprised that SQL
> Server can't use the value from the index...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ekLNvJE5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > Confirmed:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> >
> > |--Compute
> >
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> > Name], Convert(space(1)), Convert(''))))
> > |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> > OBJECT:([Northwind].[dbo].[Customers]))
> > |--Index
> >
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> > ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> >
> > :-)
> >|||On Wed, 15 Dec 2004 07:35:02 -0800, "Neil Hambly"
<hambly_neil@.hotmail.com> wrote:
>Does anyone have any ideas on recoding or indexes changes to help here
If you have a LOT of this kind of thing, how about using the full-text
indexing?
J.

Index Speed use of REPLACE in clause

[Customers].[CompanyNameNoSpace]=replace([Customers].[Company
Name], Convert(space(1)), Convert(''))))
|--Bookmark Lookup(BOOKMARK

[Bmk1000]),
OBJECT

[Northwind].[dbo].[Customers]))
|--Index
Seek(OBJECT

[Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
), SEEK

[Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
:-)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C2E4BC.868B93CB@.toomuchspamalready.nl...[vbcol=seagreen]
> I could be wrong. I thought that maybe indexes on computed columns would
> not be automatically used in Standard Edition, just as indexes on views
> are not automatically used (unless you use Enterprise Edition or provide
> special hints).
> But as I said: I could be wrong. Maybe someone with Standard Edition can
> test this example to see if an index seek is used.
> USE Northwind
> GO
> ALTER TABLE Customers ADD CompanyNameNoSpace AS
> Replace(CompanyName,space(1),'')
> GO
> CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> Customers(CompanyNameNoSpace)
> GO
> SET SHOWPLAN_TEXT ON
> GO
> SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> GO
> SET SHOWPLAN_TEXT OFF
> Gert-Jan
> Adam Machanic wrote:
|||Thanks,
Gert-Jan
Adam Machanic wrote:[vbcol=seagreen]
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> |--Compute
> Scalar(DEFINE

[Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK

[Bmk1000]),
> OBJECT

[Northwind].[dbo].[Customers]))
> |--Index
> Seek(OBJECT

[Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK

[Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C2E4BC.868B93CB@.toomuchspamalready.nl...

Friday, March 23, 2012

index on separate physical disk

Wednesday, March 21, 2012

index on separate physical disk

does anyone have any data on performance increase due to separating
data and indexes - each on a different physical drive? thanks,
arthurSomething like that depends on a lot of factors and is hard to quantify as
such. I don't think you will find a white paper or anything official along
those lines due to the fact there are so many variables. Do you have a
specific question in mind?
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109187034.716627.83110@.g14g2000cwa.googlegroups.com...
> does anyone have any data on performance increase due to separating
> data and indexes - each on a different physical drive? thanks,
> arthur
>|||well, we've got several huge tables, and queries against them that are
suffering from poor performance. both data and indexes are on a single
raid 0+1 drive. we've got several bays left in our disk array, and i'm
wondering if moving the indexes to their own raid 0+1 or raid 5 drive
will increase performance ( - in oracle this is a no brainer). you can
assume that the queries and indexes themselves are optimized.|||also, these huge tables have both clustered and non-clustered indexes
on them, and most of the slow running queries involve joins on
non-clustered indexes.|||you could consider moving individual tables and indexes to their own
drives\filegroups to improve performance. But you would want the filegroup
to be no the same server (not on a different machine somewhere).
how many disks (Physical) are in the 0+1 array ?
How much Write cache does your RAID Controller have ?
You do not want to go to a RAID 5 volume. The performance penalty in
comparison to 0+1 would kill you
Greg Jackson
PDX, Oregon|||Have you done any research to see why they are slow? What do the query
plans look like? Are you scanning the indexes of doing seeks? Have you tried
tuning the queries and or indexes? Yes it is possible that if you split
some of the indexes onto a different drive array you can get better
performance if you are maxing out the current array or channel. If you are
then you should see why that is. Have you looked at the disk counters to
see what is going on as well?
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109194461.051461.129970@.l41g2000cwc.googlegroups.com...
> also, these huge tables have both clustered and non-clustered indexes
> on them, and most of the slow running queries involve joins on
> non-clustered indexes.
>|||> Have you looked at the disk counters to
> see what is going on as well?
Right... unless you are using old IDE disks baled together with hay wire, it
is doubtful that disk is your #1 bottleneck against a nc index... you might
be able to squeeze a little more performance by moving some things to
different disks, but that will be a lot of effort for little gain in my
experience, and certainly not a silver bullet solution.|||1. 4 physical disks on the 0+1 array.
2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
we're playing around w/ the settings.
3. is that a performance penalty in reads or writes? according to
http://www.acnc.com/04_01_05.html, raid 5 has the highest read
transaction rate.
4. the queries are slow because of bad database, application, and query
design. we don't have the resources, however, to spend on fixing these.
we need a quick solution that will keep performance at an acceptable
level while we focus our resources on our application rewrite.
5. the query plans show a lot of index scans which are unavoidable. our
users can search on members w/ conditions like, last_name = 'd%'. our
primary key is not on last_name, hence the index scan.
6. yes, we've tuned the queries and indexes as much as possible
(without modifiying table structure and application code).
7. yes, we routinely check serveral performance counters, including
disk counters, and i/o isn't much of an issue.|||see notes below:
> 1. 4 physical disks on the 0+1 array.
> 2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
> we're playing around w/ the settings.
Change this to 100%. As Andrew pointed out to me last week, SQL Server
Caches Reads anyway. When I changed my controller setting sto 100% Writes,
it boosted IO significantly.
> 3. is that a performance penalty in reads or writes? according to
> http://www.acnc.com/04_01_05.html, raid 5 has the highest read
> transaction rate.
Penalty on Writes
> 4. the queries are slow because of bad database, application, and query
> design. we don't have the resources, however, to spend on fixing these.
> we need a quick solution that will keep performance at an acceptable
> level while we focus our resources on our application rewrite.
so throw hardware at this bad boy eh ?
> 5. the query plans show a lot of index scans which are unavoidable. our
> users can search on members w/ conditions like, last_name = 'd%'. our
> primary key is not on last_name, hence the index scan.
You need AN Index on Lastname. Even with the Wildcard, the index will be
used. Does not have to be a clustered index and does not have to be the PKey
> 6. yes, we've tuned the queries and indexes as much as possible
> (without modifiying table structure and application code).
Are you sure, you cant improve the index usage at all ? when you say you are
seeing scans, that to me means there IS room for improvement.
> 7. yes, we routinely check serveral performance counters, including
> disk counters, and i/o isn't much of an issue.
IO is not much of an issue ? You dont see any Disk Queueing ? With the
Scans, I would guess you are seeing some queueing
Greg Jackson
PDX, Oregon|||I agree with everything Greg stated here. If I/O isn't an issue then why do
you think adding more drives will help? If it is an issue then the scans
are most likely the cause. Even if you have to add more indexes than you
want it may get you through the tough times until you can spend time fixing
everything. Any chance you can add a covering index? There are some things
that throwing hardware at simply can not fix unless you can add enough ram
and cpus to over come the scans.
--
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23yw3smfGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> see notes below:
>
>> 1. 4 physical disks on the 0+1 array.
>> 2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
>> we're playing around w/ the settings.
> Change this to 100%. As Andrew pointed out to me last week, SQL Server
> Caches Reads anyway. When I changed my controller setting sto 100% Writes,
> it boosted IO significantly.
>
>> 3. is that a performance penalty in reads or writes? according to
>> http://www.acnc.com/04_01_05.html, raid 5 has the highest read
>> transaction rate.
> Penalty on Writes
>> 4. the queries are slow because of bad database, application, and query
>> design. we don't have the resources, however, to spend on fixing these.
>> we need a quick solution that will keep performance at an acceptable
>> level while we focus our resources on our application rewrite.
> so throw hardware at this bad boy eh ?
>> 5. the query plans show a lot of index scans which are unavoidable. our
>> users can search on members w/ conditions like, last_name = 'd%'. our
>> primary key is not on last_name, hence the index scan.
> You need AN Index on Lastname. Even with the Wildcard, the index will be
> used. Does not have to be a clustered index and does not have to be the
> PKey
>
>> 6. yes, we've tuned the queries and indexes as much as possible
>> (without modifiying table structure and application code).
> Are you sure, you cant improve the index usage at all ? when you say you
> are seeing scans, that to me means there IS room for improvement.
>
>> 7. yes, we routinely check serveral performance counters, including
>> disk counters, and i/o isn't much of an issue.
> IO is not much of an issue ? You dont see any Disk Queueing ? With the
> Scans, I would guess you are seeing some queueing
>
> Greg Jackson
> PDX, Oregon
>|||These gurus covered it all. I can't agree more about the indexes. If you
know which fields are being searched, especially frequently, add indexes if
they're not already there. You might get a little boost out of your
existing indexes by running UPDATE STATISTICS and DBCC INDEXDEFRAG on your
tables and indexes also.|||Just make sure you do it in this order DBCC INDEXDEFRAG and then UPDATE
STATISTICS<g>.
--
Andrew J. Kelly SQL MVP
"Michael C#" <xyz@.abcdef.com> wrote in message
news:JTbTd.19988$8m4.18245@.fe08.lga...
> These gurus covered it all. I can't agree more about the indexes. If you
> know which fields are being searched, especially frequently, add indexes
> if they're not already there. You might get a little boost out of your
> existing indexes by running UPDATE STATISTICS and DBCC INDEXDEFRAG on your
> tables and indexes also.
>|||Good point :) Thx
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Vmg7mnGFHA.2744@.tk2msftngp13.phx.gbl...
> Just make sure you do it in this order DBCC INDEXDEFRAG and then UPDATE
> STATISTICS<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:JTbTd.19988$8m4.18245@.fe08.lga...
>> These gurus covered it all. I can't agree more about the indexes. If
>> you know which fields are being searched, especially frequently, add
>> indexes if they're not already there. You might get a little boost out
>> of your existing indexes by running UPDATE STATISTICS and DBCC
>> INDEXDEFRAG on your tables and indexes also.
>|||> unless you can add enough ram and cpus to over come the scans.
exactly! again, part of the problem w/ scans is that users can search
(for example on a member) via any of a number of fields, each of which
have an associated non-clustered index. if they search for a particular
user, e.g. where last_name = 'smith', then performance is fine,
however, when they do, where last_name like 's%', performance degrades.
some of this is a training issue, but still.
also, we've got a job that runs indexdefrag nightly.
anyway, i ran some tests last night by creating a copy of one of our
large tables and putting the associated indexes on a separate drive
(raid 5). i ran a number of (select) queries against both the large
table and copy of large table (issuing dbcc freeproccache and
dropcleanbuffers before each run), and saw a performance difference of
0 to 27% depending on the complexity of the query.
thanks for all your help so far on this!|||> user, e.g. where last_name = 'smith', then performance is fine,
> however, when they do, where last_name like 's%', performance degrades.
Actually, this isn't always true. When you use LIKE 's%' there is a very
good chance that the index will be utilized, however when you use LIKE
'%s...' that is when you will likely see a change in the plan.
A|||fyi: here's an article that suggest splitting data and indexes,
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=42|||Don't forget to UPDATE STATISTICS also. The select ... where last_name like
's%' query should be able to take advantage of your indexes in its query
plan. You might try running the Index Tuning Wizard from Enterprise Manager
to help optimize your indexes. Based on the scans, it sounds like your
indexes could use some optimization.
"arthur" <alangham@.gmail.com> wrote in message
news:1109265280.958546.16260@.f14g2000cwb.googlegroups.com...
>> unless you can add enough ram and cpus to over come the scans.
> exactly! again, part of the problem w/ scans is that users can search
> (for example on a member) via any of a number of fields, each of which
> have an associated non-clustered index. if they search for a particular
> user, e.g. where last_name = 'smith', then performance is fine,
> however, when they do, where last_name like 's%', performance degrades.
> some of this is a training issue, but still.
> also, we've got a job that runs indexdefrag nightly.
> anyway, i ran some tests last night by creating a copy of one of our
> large tables and putting the associated indexes on a separate drive
> (raid 5). i ran a number of (select) queries against both the large
> table and copy of large table (issuing dbcc freeproccache and
> dropcleanbuffers before each run), and saw a performance difference of
> 0 to 27% depending on the complexity of the query.
> thanks for all your help so far on this!
>sql

Monday, March 12, 2012

Index hints in a view.

Hi
SQL Server 2000 SP4
I am using view in my stored procedure. The view gets data from only one
table. I can not use table directly due to some reasons. I would like to add
an index hint to this table. Is View definition the only place for that? I
tried to add hint index to the view directly in the FROM clause of my SP but
i got a message that the hint is ignored.
Thanks in advance.For the view, you can do index hints on indexed views only. Otherwise, to
specify a specific index for a table in the view, you will need to modify th
e
view and supply the table hint for the index you want to use for the require
d
table using the WITH INDEX () hint.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Alexander Korol" wrote:

> Hi
> SQL Server 2000 SP4
> I am using view in my stored procedure. The view gets data from only one
> table. I can not use table directly due to some reasons. I would like to a
dd
> an index hint to this table. Is View definition the only place for that? I
> tried to add hint index to the view directly in the FROM clause of my SP b
ut
> i got a message that the hint is ignored.
> Thanks in advance.

Friday, March 9, 2012

Index Fragmenation

I'm having trouble with slow performance due to index fragmentation. I'll insert 20000 records into a table and then have index fragmentation of like 67%! The database is so slow query this data back (over 3 minutes!). After rebuilding the index on the primary key (the only index) the same query takes less than 2 seconds. I'm using GUID as my primary key and I have recently switched from using NEWID() to using the new NEWSEQUENTAILID() to generate them. Can anyone suggest why I'm still having such a hard time with fragmentation?Unless you really NEED a GUID because you need an id that HAS to be unique across an entire network, I'd use an integer or bigint. There are a lot of articles out there regarding the performance hit associated with GUID's primarily due to page splits if memory serves. Is your primary key also a clustered index?
|||Yes it is a clustered index. I thought the problem with GUIDs in general is that they are pseudo-random. That is why I went to the newsequentialid() function that generates sequential guids across a machine.|||Index fragementation caused by using GUIDs in indexed columns shouldn't be causing the issues you're seeing.

While it is certainly true that you'll see very high index fragmentation for these columns (our production DB often has 95%+ fragmentation), this isn't necessarily going to kill your performance. We did extensive performance comparisons before decided to go with nearly 100% GUIDs are primary keys. There was certainly a perf difference, but it was negligible overall.

In fact, INSERT performance may actually increase thanks to the fact that disk hot spots are far less common.

I would examine the query plans before/after your defragmentation/rebuild your index. I think something else must be going on here.|||Its very likely that you have out of date statistics on the table that is giving you a bad query plan after the inserts. Rebuilding the index automatically updates statistics. Try just running UPDATE STATISTICS TableName after the INSERT and see if that gives you a better query plan.

Friday, March 30, 2012

Index Speed use of REPLACE in clause

Index Speed use of REPLACE in clause

Friday, March 23, 2012

index on separate physical disk

Wednesday, March 21, 2012

index on separate physical disk

index on separate physical disk

Monday, March 12, 2012

Index hints in a view.

Friday, March 9, 2012

Index Fragmenation

index and key

Blog Archive

About Me