Indexes for a newbie ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Indexes for a newbie ...

Author

Topic

fulltilt
Starting Member

6 Posts

Posted - 2007-11-28 : 19:03:19

Hi,

I'm new to SQL Server 2005, but I have been using Oracle for years so I understand the basics of tables, indexing, etc...

I have a table with 175 million rows (yes, 175000000) and obviously the performance is quite bad. There is an index on the most popular date column but I'm trying to work out when the index is used and when not.

At the moment I'm trying to do various date slices like :

select count(*) from tablenamehere
where date_created > Dateadd(d, -7, getdate())

but the performance is terrible, and seems to be the same whether I use the indexed column or the non-indexed column

Any ideas ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 19:10:13

Post the DDL for your table including constraints and indexes.

Also post the execution plan for your query so that we can see if it is using the most efficient path.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

fulltilt
Starting Member

6 Posts

Posted - 2007-11-28 : 19:14:54

DDL :


USE [dbnamegoeshere]
GO
/****** Object:  Table [dbo].[FACT_WAP_SESSIONS]    Script Date: 11/29/2007 11:06:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FACT_WAP_SESSIONS](
	[FACT_WAP_SESSIONS_KEY] [int] IDENTITY(1,1) NOT NULL,
	[WAP_SESSION_ID] [int] NOT NULL,
	[PAGE_HIT_ID] [int] NOT NULL,
	[DATE_CREATED] [datetime] NOT NULL,
	[CLIENTS_DIMENSION_KEY] [int] NOT NULL,
	[MSISDN] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DATE_TIME_CREATED_KEY] [datetime] NOT NULL,
	[NETWORK_TYPES_DIMENSION_KEY] [int] NOT NULL,
	[DEVICES_DIMENSION_KEY] [int] NOT NULL,
	[PAGE_DIMENSION_KEY] [int] NOT NULL
) ON [PRIMARY]

GO
USE [MIA_DW]
GO
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_CLIENTS] FOREIGN KEY([CLIENTS_DIMENSION_KEY])
REFERENCES [dbo].[CLIENTS] ([CLIENTS_DIMENSION_KEY])
GO
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_DATE_TIME] FOREIGN KEY([DATE_TIME_CREATED_KEY])
REFERENCES [dbo].[DATE_TIME] ([PK_Date_Time])
GO
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_DEVICES] FOREIGN KEY([DEVICES_DIMENSION_KEY])
REFERENCES [dbo].[DEVICES] ([DEVICES_DIMENSION_KEY])
GO
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_NETWORK_TYPES] FOREIGN KEY([NETWORK_TYPES_DIMENSION_KEY])
REFERENCES [dbo].[NETWORK_TYPES] ([NETWORK_TYPES_DIMENSION_KEY])

Execution plan .... what is the best way to post that ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 19:19:08

I don't see any indexes on your table.

For the execution plan, I prefer to look at the image version rather than the text version. To post an image though, you'll need to upload it to some web server and then provide a link here to it. For the text version, you'd specify the SET SHOWPLAN_TEXT ON. Perhaps try SET SHOWPLAN_XML ON and I'll see if SQL Profiler can load it graphically for me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

fulltilt
Starting Member

6 Posts

Posted - 2007-11-28 : 19:22:02

Sorry, as I said - I'm new and trying to use SSMS for the first time. If there's an easier way, please let me know.

I'll post the image of the execution plan in a few minutes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 19:29:57

Right click on the database in SSMS, go to Tasks, Generate Scripts. Walk through the wizard and make sure you change the option to True for Script Indexes on the Options page. Also make sure that you only script this one table since we wouldn't want you to post your entire schema here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

fulltilt
Starting Member

6 Posts

Posted - 2007-11-28 : 19:33:15

OK, I'll generate the scripts now

I should not that the date_created column has no indexes on it, there is another datetime column that is indexed, but the performance is the same regardless of which column you use.

fulltilt
Starting Member

6 Posts

Posted - 2007-11-28 : 19:36:28

Full script (I hope) :


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FACT_WAP_SESSIONS](
	[FACT_WAP_SESSIONS_KEY] [int] IDENTITY(1,1) NOT NULL,
	[WAP_SESSION_ID] [int] NOT NULL,
	[PAGE_HIT_ID] [int] NOT NULL,
	[DATE_CREATED] [datetime] NOT NULL,
	[CLIENTS_DIMENSION_KEY] [int] NOT NULL,
	[MSISDN] [nvarchar](50) NULL,
	[DATE_TIME_CREATED_KEY] [datetime] NOT NULL,
	[NETWORK_TYPES_DIMENSION_KEY] [int] NOT NULL,
	[DEVICES_DIMENSION_KEY] [int] NOT NULL,
	[PAGE_DIMENSION_KEY] [int] NOT NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]') AND name = N'IX_FACT_WAP_SESSIONS')
CREATE CLUSTERED INDEX [IX_FACT_WAP_SESSIONS] ON [dbo].[FACT_WAP_SESSIONS] 
(
	[DATE_TIME_CREATED_KEY] ASC,
	[MSISDN] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]') AND name = N'IX_FACT_WAP_SESSIONS_PAGE_HIT_ID')
CREATE NONCLUSTERED INDEX [IX_FACT_WAP_SESSIONS_PAGE_HIT_ID] ON [dbo].[FACT_WAP_SESSIONS] 
(
	[PAGE_HIT_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FACT_WAP_SESSIONS_CLIENTS]') AND parent_object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]'))
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_CLIENTS] FOREIGN KEY([CLIENTS_DIMENSION_KEY])
REFERENCES [dbo].[CLIENTS] ([CLIENTS_DIMENSION_KEY])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FACT_WAP_SESSIONS_DATE_TIME]') AND parent_object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]'))
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_DATE_TIME] FOREIGN KEY([DATE_TIME_CREATED_KEY])
REFERENCES [dbo].[DATE_TIME] ([PK_Date_Time])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FACT_WAP_SESSIONS_DEVICES]') AND parent_object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]'))
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_DEVICES] FOREIGN KEY([DEVICES_DIMENSION_KEY])
REFERENCES [dbo].[DEVICES] ([DEVICES_DIMENSION_KEY])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FACT_WAP_SESSIONS_NETWORK_TYPES]') AND parent_object_id = OBJECT_ID(N'[dbo].[FACT_WAP_SESSIONS]'))
ALTER TABLE [dbo].[FACT_WAP_SESSIONS]  WITH CHECK ADD  CONSTRAINT [FK_FACT_WAP_SESSIONS_NETWORK_TYPES] FOREIGN KEY([NETWORK_TYPES_DIMENSION_KEY])
REFERENCES [dbo].[NETWORK_TYPES] ([NETWORK_TYPES_DIMENSION_KEY])

fulltilt
Starting Member

6 Posts

Posted - 2007-11-28 : 19:49:19

Hhhhmmm .... I just checked the times for both queries and the second one is performing a LOT better than the first

.. that IS good news, but contradictory to what I thought it was doing earlier.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 19:53:31

The reason for this discrepancy is that you have an index that includes date_time_created_key (first column of the clustered index). There is no index on date_created according to the DDL that you posted.

I'd suggest creating a non-clustered index on date_created column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

pootle_flump

1064 Posts

Posted - 2007-11-29 : 06:47:25

It also depends what your real queries are doing. Tara's suggestion will sort out your posted SQL.

Are you aware of the difference between a seek and a scan? Do you know why one is very efficient and the other not? Also, a massive clue (ignoring how long these two queries take to run) is to compare the "Query cost (relative to batch): ##%". You can see that the efficient one doesn't even register a cost compared to the table scan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources