index and key: logged

Showing posts with label logged. Show all posts

Monday, March 26, 2012

Index Rebuild fails over SET options

Wednesday, March 21, 2012

Index on result of function

I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)

The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?

Yes. You can achive this using the indexed views.

Create different views for each year & index it.

|||Bes, this table sounds like a good candidate for the new table partitioning method of SQL 2005. You could partition by the year and month... There would be separate indexes on each partition slice and SQL Server would direct a query to just the partition needed and the query would run much faster... but... you need Enterprise Edition for paritioning. If you have Enterprise, then it's something to check out... Bruce|||

Bruce,

It's good to know there is another way to do it. The little I've read about Indexed Views indicates they'll increase my maintenance and I should only use them in special cases.

We're not running Enterprise (too much $ for dual CPUs), but if depending on how we use this data maybe we'll be able to justify it.

Thanks!

Brian

|||

I think creating a couple of computed column(s) and creating an index on those field(s) will give you the best combination of query performance and maintenance. Lots of modifications to data in the base table in an indexed view could cause a server to grind to a halt. The index maintenance on the computed columns should be minimal.

alter table MyTable add MyDateYear AS YEAR(MyDate)

alter table MyTable add MyDateMonth AS Month(MyDate)

CREATE INDEX IX_MyTable_Year_Month ON MyTable(MyDateYear, MyDateMonth)

Wednesday, March 7, 2012

Index Fails over SET options

This came up before and it did not get fixed correctly on my end. Anyone?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'BIGSERVER' as OURCORP\BIGSERVER (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
[1] Database DB1: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'tAmexDemands'
Rebuilding indexes for table 'tAmexDemandsHistory'
Rebuilding indexes for table 'tAthensWarning'
Rebuilding indexes for table 'tATPOLines'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Regards,
Jamie
Hi
For indexes on Computed columns or views you need this setting to be ON,
DBCC USEROPTIONS will show you what the current settings are and you can use
the SET command to set them. If you want to change the system wide use
sp_configure to change the 'user options' value or sp_dboption to set the
specific values at database level.
John
"thejamie" wrote:

> This came up before and it did not get fixed correctly on my end. Anyone?
> --
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'tAmexDemands'
> Rebuilding indexes for table 'tAmexDemandsHistory'
> Rebuilding indexes for table 'tAthensWarning'
> Rebuilding indexes for table 'tATPOLines'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> --
> Regards,
> Jamie
|||The database is inherited and no one seems to know where the computed columns
exist. It is a rather large database (roughly 40 gig on the primary and 60
on the history filegroup). I'm running into problems searching for these
conditions. If I simply change options on the database, it will likely
present problems. I used a routine on the MSDN to rebuild indexes, but
there is no time to run it. TDay is filled up (11/23) with catch up
processes. Rebuilding indexes on this particular database is not an option
although some get rebuilt at night but not all. Also not all four processors
are used, only one. Apparently there is concern the production server is
about to crash. A backup plan is being implemented in this event, but there
are bottlenecks due to the physical size of the disks. The processor spikes
into the 90 % area and averages in the 75% during peak production time.
While the machine manages during the day, it is failing to do the work at
night. Not sure what other options may be available.
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> For indexes on Computed columns or views you need this setting to be ON,
> DBCC USEROPTIONS will show you what the current settings are and you can use
> the SET command to set them. If you want to change the system wide use
> sp_configure to change the 'user options' value or sp_dboption to set the
> specific values at database level.
> John
>
> "thejamie" wrote:

Index Fails over SET options

This came up before and it did not get fixed correctly on my end. Anyone?
--
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'BIGSERVER' as OURCORP\BIGSERVER (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
[1] Database DB1: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'tAmexDemands'
Rebuilding indexes for table 'tAmexDemandsHistory'
Rebuilding indexes for table 'tAthensWarning'
Rebuilding indexes for table 'tATPOLines'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
--
Regards,
JamieHi
For indexes on Computed columns or views you need this setting to be ON,
DBCC USEROPTIONS will show you what the current settings are and you can use
the SET command to set them. If you want to change the system wide use
sp_configure to change the 'user options' value or sp_dboption to set the
specific values at database level.
John
"thejamie" wrote:

> This came up before and it did not get fixed correctly on my end. Anyone?
> --
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'tAmexDemands'
> Rebuilding indexes for table 'tAmexDemandsHistory'
> Rebuilding indexes for table 'tAthensWarning'
> Rebuilding indexes for table 'tATPOLines'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> --
> Regards,
> Jamie|||The database is inherited and no one seems to know where the computed column
s
exist. It is a rather large database (roughly 40 gig on the primary and 60
on the history filegroup). I'm running into problems searching for these
conditions. If I simply change options on the database, it will likely
present problems. I used a routine on the MSDN to rebuild indexes, but
there is no time to run it. TDay is filled up (11/23) with catch up
processes. Rebuilding indexes on this particular database is not an option
although some get rebuilt at night but not all. Also not all four processor
s
are used, only one. Apparently there is concern the production server is
about to crash. A backup plan is being implemented in this event, but there
are bottlenecks due to the physical size of the disks. The processor spike
s
into the 90 % area and averages in the 75% during peak production time.
While the machine manages during the day, it is failing to do the work at
night. Not sure what other options may be available.
--
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> For indexes on Computed columns or views you need this setting to be ON,
> DBCC USEROPTIONS will show you what the current settings are and you can u
se
> the SET command to set them. If you want to change the system wide use
> sp_configure to change the 'user options' value or sp_dboption to set the
> specific values at database level.
> John
>
> "thejamie" wrote:
>

Index Fails over SET options

This came up before and it did not get fixed correctly on my end. Anyone?
--
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'BIGSERVER' as OURCORP\BIGSERVER (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
[1] Database DB1: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'tAmexDemands'
Rebuilding indexes for table 'tAmexDemandsHistory'
Rebuilding indexes for table 'tAthensWarning'
Rebuilding indexes for table 'tATPOLines'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
--
Regards,
JamieHi
For indexes on Computed columns or views you need this setting to be ON,
DBCC USEROPTIONS will show you what the current settings are and you can use
the SET command to set them. If you want to change the system wide use
sp_configure to change the 'user options' value or sp_dboption to set the
specific values at database level.
John
"thejamie" wrote:
> This came up before and it did not get fixed correctly on my end. Anyone?
> --
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'tAmexDemands'
> Rebuilding indexes for table 'tAmexDemandsHistory'
> Rebuilding indexes for table 'tAthensWarning'
> Rebuilding indexes for table 'tATPOLines'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> --
> Regards,
> Jamie|||The database is inherited and no one seems to know where the computed columns
exist. It is a rather large database (roughly 40 gig on the primary and 60
on the history filegroup). I'm running into problems searching for these
conditions. If I simply change options on the database, it will likely
present problems. I used a routine on the MSDN to rebuild indexes, but
there is no time to run it. TDay is filled up (11/23) with catch up
processes. Rebuilding indexes on this particular database is not an option
although some get rebuilt at night but not all. Also not all four processors
are used, only one. Apparently there is concern the production server is
about to crash. A backup plan is being implemented in this event, but there
are bottlenecks due to the physical size of the disks. The processor spikes
into the 90 % area and averages in the 75% during peak production time.
While the machine manages during the day, it is failing to do the work at
night. Not sure what other options may be available.
--
Regards,
Jamie
"John Bell" wrote:
> Hi
> For indexes on Computed columns or views you need this setting to be ON,
> DBCC USEROPTIONS will show you what the current settings are and you can use
> the SET command to set them. If you want to change the system wide use
> sp_configure to change the 'user options' value or sp_dboption to set the
> specific values at database level.
> John
>
> "thejamie" wrote:
> > This came up before and it did not get fixed correctly on my end. Anyone?
> > --
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> > Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> > [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> >
> > Rebuilding indexes for table 'tAmexDemands'
> > Rebuilding indexes for table 'tAmexDemandsHistory'
> > Rebuilding indexes for table 'tAthensWarning'
> > Rebuilding indexes for table 'tATPOLines'
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]DBCC failed because the following SET options have
> > incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> > --
> > Regards,
> > Jamie

Monday, March 26, 2012

Index Rebuild fails over SET options

Index Rebuild fails over SET options

Index Rebuild fails over SET options

Wednesday, March 21, 2012

Index on result of function

Wednesday, March 7, 2012

Index Fails over SET options

Index Fails over SET options

Index Fails over SET options

index and key

Blog Archive

About Me