Faster Indexes using order by statement

Hello all,
I have a specific sql statement and I am looking to return the result
set a lot faster. Currently the setup is like so:
myTable schema:
Counterdecimal9 (pk)
Machinevarchar60
LogEntryvarchar1000
Activevarchar50
SysInfovarchar255
Idlevarchar50
IPvarchar15
KioskDatedatetime
KioskTimedatetime
ServerDatedatetime
ServerTimedatetime
Applicationvarchar15
WebDomainvarchar50
NSCodevarchar10
There is a Clustered index on Counter and two Non-clustered:
1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
fill factor 90% on PRIMARY
sql statment:
select top 1000 machine, logentry,
convert(varchar(8),kioskdate,5) as Kiosk_date,
convert(varchar(8),kiosktime,8) as Kiosk_time,
convert(varchar(8),serverdate,5) as ServerDate,
convert(varchar(8),serverdate,8) as ServerTime,
application,
nscode,
webdomain
from myTable
where machine = 'machinename'
order by kioskdate desc, kiosktime desc
Currently the table holds over 18 million records and the above search
returns in under 3 seconds, however when I change the order by
statement to ServerDate desc only (which is what I need), it takes
over four minutes. I have tried altering/tweaking the indexes, but
have had no success.
Any ideas greatly appreciated.
Thanks
Scott
The only index that would be useful for that query is
Counter, Machine, NSCode, KioskDate, KioskTime
And that only for a scan as Counter is the first field.
How unique is machinename - if it's good then put an index on that.
Not sure how the optimiser would handle it but try an index
machinename, kioskdate desc, kiosktime desc
This will be in the oerder of the required resultset and the server could
take the top thousand entries for the machinename without any other
processing. Doubt if it will do that but it's worth a try.
You might be able to give it hint by finding the range of dates required and
using that.
"scott" wrote:

> Hello all,
> I have a specific sql statement and I am looking to return the result
> set a lot faster. Currently the setup is like so:
> myTable schema:
>
> Counterdecimal9 (pk)
> Machinevarchar60
> LogEntryvarchar1000
> Activevarchar50
> SysInfovarchar255
> Idlevarchar50
> IPvarchar15
> KioskDatedatetime
> KioskTimedatetime
> ServerDatedatetime
> ServerTimedatetime
> Applicationvarchar15
> WebDomainvarchar50
> NSCodevarchar10
> There is a Clustered index on Counter and two Non-clustered:
> 1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
> 2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
> fill factor 90% on PRIMARY
>
> sql statment:
> select top 1000 machine, logentry,
> convert(varchar(8),kioskdate,5) as Kiosk_date,
> convert(varchar(8),kiosktime,8) as Kiosk_time,
> convert(varchar(8),serverdate,5) as ServerDate,
> convert(varchar(8),serverdate,8) as ServerTime,
> application,
> nscode,
> webdomain
> from myTable
> where machine = 'machinename'
> order by kioskdate desc, kiosktime desc
>
> Currently the table holds over 18 million records and the above search
> returns in under 3 seconds, however when I change the order by
> statement to ServerDate desc only (which is what I need), it takes
> over four minutes. I have tried altering/tweaking the indexes, but
> have had no success.
>
> Any ideas greatly appreciated.
> Thanks
> Scott
>
|||Scott,
both your nonclustered indexes are not very useful (ever). This is
because Counter is a unique column and is already indexed.
As Nigel suggested, if "machine = 'machinename'" is highly selective
(returns just a few percent of all rows), then your query could benefit
from an index on (machinename, kioskdate, kiosktime). Specifying
ascending or descending in the index definition is not useful for your
query.
If the expression "machine = 'machinename'" returns a very high
percentage of all rows, then the query could benefit from an index on
(kioskdate, kiosktime, machinename).
You could also try the Index Tuning Wizard and see what that comes up
with.
Hope this helps,
Gert-Jan
scott wrote:
> Hello all,
> I have a specific sql statement and I am looking to return the result
> set a lot faster. Currently the setup is like so:
> myTable schema:
> Counter decimal 9 (pk)
> Machine varchar 60
> LogEntry varchar 1000
> Active varchar 50
> SysInfo varchar 255
> Idle varchar 50
> IP varchar 15
> KioskDate datetime
> KioskTime datetime
> ServerDate datetime
> ServerTime datetime
> Application varchar 15
> WebDomain varchar 50
> NSCode varchar 10
> There is a Clustered index on Counter and two Non-clustered:
> 1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
> 2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
> fill factor 90% on PRIMARY
> sql statment:
> select top 1000 machine, logentry,
> convert(varchar(8),kioskdate,5) as Kiosk_date,
> convert(varchar(8),kiosktime,8) as Kiosk_time,
> convert(varchar(8),serverdate,5) as ServerDate,
> convert(varchar(8),serverdate,8) as ServerTime,
> application,
> nscode,
> webdomain
> from myTable
> where machine = 'machinename'
> order by kioskdate desc, kiosktime desc
> Currently the table holds over 18 million records and the above search
> returns in under 3 seconds, however when I change the order by
> statement to ServerDate desc only (which is what I need), it takes
> over four minutes. I have tried altering/tweaking the indexes, but
> have had no success.
> Any ideas greatly appreciated.
> Thanks
> Scott
(Please reply only to the newsgroup)
|||Hi,
many thanks for all your replies, however I'm still have no joy on
this. Whatever non-clustered indexes I create, searching by kioskdate
and time (desc) is still way faster than searchig by serverdate (desc)
which is what I want.
Using the Index Tuning Wizard gives me nothing whichever way I do it
(create new indexes, or scan with existing indexes).
Back to the drawing board methinks!
Cheers
Scott

Faster Indexes using order by statement

Hello all,
I have a specific sql statement and I am looking to return the result
set a lot faster. Currently the setup is like so:
myTable schema:
Counter decimal 9 (pk)
Machine varchar 60
LogEntry varchar 1000
Active varchar 50
SysInfo varchar 255
Idle varchar 50
IP varchar 15
KioskDate datetime
KioskTime datetime
ServerDate datetime
ServerTime datetime
Application varchar 15
WebDomain varchar 50
NSCode varchar 10
There is a Clustered index on Counter and two Non-clustered:
1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
fill factor 90% on PRIMARY
sql statment:
select top 1000 machine, logentry,
convert(varchar(8),kioskdate,5) as Kiosk_date,
convert(varchar(8),kiosktime,8) as Kiosk_time,
convert(varchar(8),serverdate,5) as ServerDate,
convert(varchar(8),serverdate,8) as ServerTime,
application,
nscode,
webdomain
from myTable
where machine = 'machinename'
order by kioskdate desc, kiosktime desc
Currently the table holds over 18 million records and the above search
returns in under 3 seconds, however when I change the order by
statement to ServerDate desc only (which is what I need), it takes
over four minutes. I have tried altering/tweaking the indexes, but
have had no success.
Any ideas greatly appreciated.
Thanks
ScottThe only index that would be useful for that query is
Counter, Machine, NSCode, KioskDate, KioskTime
And that only for a scan as Counter is the first field.
How unique is machinename - if it's good then put an index on that.
Not sure how the optimiser would handle it but try an index
machinename, kioskdate desc, kiosktime desc
This will be in the oerder of the required resultset and the server could
take the top thousand entries for the machinename without any other
processing. Doubt if it will do that but it's worth a try.
You might be able to give it hint by finding the range of dates required and
using that.
"scott" wrote:
> Hello all,
> I have a specific sql statement and I am looking to return the result
> set a lot faster. Currently the setup is like so:
> myTable schema:
>
> Counter decimal 9 (pk)
> Machine varchar 60
> LogEntry varchar 1000
> Active varchar 50
> SysInfo varchar 255
> Idle varchar 50
> IP varchar 15
> KioskDate datetime
> KioskTime datetime
> ServerDate datetime
> ServerTime datetime
> Application varchar 15
> WebDomain varchar 50
> NSCode varchar 10
> There is a Clustered index on Counter and two Non-clustered:
> 1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
> 2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
> fill factor 90% on PRIMARY
>
> sql statment:
> select top 1000 machine, logentry,
> convert(varchar(8),kioskdate,5) as Kiosk_date,
> convert(varchar(8),kiosktime,8) as Kiosk_time,
> convert(varchar(8),serverdate,5) as ServerDate,
> convert(varchar(8),serverdate,8) as ServerTime,
> application,
> nscode,
> webdomain
> from myTable
> where machine = 'machinename'
> order by kioskdate desc, kiosktime desc
>
> Currently the table holds over 18 million records and the above search
> returns in under 3 seconds, however when I change the order by
> statement to ServerDate desc only (which is what I need), it takes
> over four minutes. I have tried altering/tweaking the indexes, but
> have had no success.
>
> Any ideas greatly appreciated.
> Thanks
> Scott
>|||Scott,
both your nonclustered indexes are not very useful (ever). This is
because Counter is a unique column and is already indexed.
As Nigel suggested, if "machine = 'machinename'" is highly selective
(returns just a few percent of all rows), then your query could benefit
from an index on (machinename, kioskdate, kiosktime). Specifying
ascending or descending in the index definition is not useful for your
query.
If the expression "machine = 'machinename'" returns a very high
percentage of all rows, then the query could benefit from an index on
(kioskdate, kiosktime, machinename).
You could also try the Index Tuning Wizard and see what that comes up
with.
Hope this helps,
Gert-Jan
scott wrote:
> Hello all,
> I have a specific sql statement and I am looking to return the result
> set a lot faster. Currently the setup is like so:
> myTable schema:
> Counter decimal 9 (pk)
> Machine varchar 60
> LogEntry varchar 1000
> Active varchar 50
> SysInfo varchar 255
> Idle varchar 50
> IP varchar 15
> KioskDate datetime
> KioskTime datetime
> ServerDate datetime
> ServerTime datetime
> Application varchar 15
> WebDomain varchar 50
> NSCode varchar 10
> There is a Clustered index on Counter and two Non-clustered:
> 1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
> 2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
> fill factor 90% on PRIMARY
> sql statment:
> select top 1000 machine, logentry,
> convert(varchar(8),kioskdate,5) as Kiosk_date,
> convert(varchar(8),kiosktime,8) as Kiosk_time,
> convert(varchar(8),serverdate,5) as ServerDate,
> convert(varchar(8),serverdate,8) as ServerTime,
> application,
> nscode,
> webdomain
> from myTable
> where machine = 'machinename'
> order by kioskdate desc, kiosktime desc
> Currently the table holds over 18 million records and the above search
> returns in under 3 seconds, however when I change the order by
> statement to ServerDate desc only (which is what I need), it takes
> over four minutes. I have tried altering/tweaking the indexes, but
> have had no success.
> Any ideas greatly appreciated.
> Thanks
> Scott
--
(Please reply only to the newsgroup)|||Hi,
many thanks for all your replies, however I'm still have no joy on
this. Whatever non-clustered indexes I create, searching by kioskdate
and time (desc) is still way faster than searchig by serverdate (desc)
which is what I want.
Using the Index Tuning Wizard gives me nothing whichever way I do it
(create new indexes, or scan with existing indexes).
Back to the drawing board methinks!
Cheers
Scott

Failure to Remove Old Transaction Log Backups

I've just become the DBA. The origianl Maintenance Plan was setup to remove
old TRasact Log Backups. This has not worked in several months. Does anyone
have an idea why a working plan would fail? Everything appears to be set
correctly. Is there any advantage to placing optimimization in a seperate
plan from the backups?
Here is a post from Bill at MS that outlines the most common reasons for
this:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"mark_at_msb" <mark_at_msb@.discussions.microsoft.com> wrote in message
news:486897A4-7C19-4924-845D-0A724D4E4899@.microsoft.com...
> I've just become the DBA. The origianl Maintenance Plan was setup to
remove
> old TRasact Log Backups. This has not worked in several months. Does
anyone
> have an idea why a working plan would fail? Everything appears to be set
> correctly. Is there any advantage to placing optimimization in a seperate
> plan from the backups?

Failure to install - XOLEHLP.dll not found

Hello,

I'm trying to install CTP3 of katmai on Windows Server 2004 running inside Virtual PC.

It installs the setup files OK. Straight after that I get an error message box saying "The application has failed to start because XOLEHLP.DLL was not found. Re-installing the application may fix this problem"

Clicking OK gives me another message box with the message "TITLE: Microsoft SQL Server code name Katmai Setup

Failed to load SqlSpars.dll

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=10.0.1019.17&EvtSrc=setup.rll&EvtID=50005&EvtType=setup%5csqlsetupactions.cpp%40InvokeSqlSetupDllAction%40sqls%3a%3aInvokeSqlSetupDllAction%3a%3aperform%400x57

BUTTONS:

OK

"

The link doesn't give me anything useful.

Then I get the familiar 'Send Error Report' dialog. I DID send the error report.

I'm stumped. Anyone got any ideas?

Thanks

Jamie

Moving over to the new Katmai forums|||Jamie,

I assume you tried to instell it on a Win 2003 box (not 2004) :-)

Anyway, that box (or rather VM in your case), how was it created in the first place? Have you done any funky stuff with MSDTC, as xolehlp.dll is a helper file for the DTC. Also, the Katmai installation, was it from the iso or a "normal" installation?

Niels
|||

Hey there Niels,

You're right. Win2k3. DOH!!!

Interesting your comments about DTC - I'll have a play around with that. I certainly haven't done any changes on the box - its as vanilla as it gets. Standard OS install. I'm using the katmai executable - i.e. not the .iso

This is all at home and I am now at work. I'll have another play around later this evening.

Thanks

Jamie

|||OK, try the ISO and see if it helps.

Niels

Failure to install - XOLEHLP.dll not found

Hello,

I'm trying to install CTP3 of katmai on Windows Server 2004 running inside Virtual PC.

It installs the setup files OK. Straight after that I get an error message box saying "The application has failed to start because XOLEHLP.DLL was not found. Re-installing the application may fix this problem"

Clicking OK gives me another message box with the message "TITLE: Microsoft SQL Server code name Katmai Setup

Failed to load SqlSpars.dll

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=10.0.1019.17&EvtSrc=setup.rll&EvtID=50005&EvtType=setup%5csqlsetupactions.cpp%40InvokeSqlSetupDllAction%40sqls%3a%3aInvokeSqlSetupDllAction%3a%3aperform%400x57

BUTTONS:

OK

"

The link doesn't give me anything useful.

Then I get the familiar 'Send Error Report' dialog. I DID send the error report.

I'm stumped. Anyone got any ideas?

Thanks

Jamie

Moving over to the new Katmai forums|||Jamie,

I assume you tried to instell it on a Win 2003 box (not 2004) :-)

Anyway, that box (or rather VM in your case), how was it created in the first place? Have you done any funky stuff with MSDTC, as xolehlp.dll is a helper file for the DTC. Also, the Katmai installation, was it from the iso or a "normal" installation?

Niels
|||

Hey there Niels,

You're right. Win2k3. DOH!!!

Interesting your comments about DTC - I'll have a play around with that. I certainly haven't done any changes on the box - its as vanilla as it gets. Standard OS install. I'm using the katmai executable - i.e. not the .iso

This is all at home and I am now at work. I'll have another play around later this evening.

Thanks

Jamie

|||OK, try the ISO and see if it helps.

Niels