if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[log_running]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[log_running]
GO

CREATE TABLE [dbo].[log_running] (
	[pagename] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[i_users] [int] NOT NULL ,
	[userlevel] [tinyint] NOT NULL ,
	[datetime] [smalldatetime] NOT NULL ,
	[site_id] [tinyint] NOT NULL ,
	[iServers] [int] NOT NULL ,
	[i] [int] IDENTITY (1, 1) PRIMARY KEY CLUSTERED ,
	[hour] [tinyint] NOT NULL ,
	[ip] [int] NULL 
) ON [WRITE_INTENSIVE_1]
GO

 CREATE  INDEX [log_running4] ON [dbo].[log_running]([iServers], [hour], [pagename], [datetime], [site_id]) ON [PRIMARY]
GO

 

CREATE PROCEDURE p_log_runningEx
    (@page varchar(40), @iUsers int, @userlevel int,@iServers int,@SiteID int=1,@vcIP varchar(15)=NULL)
AS
set nocount on

/* Add pageview to log_running */
insert into log_running WITH(FASTFIRSTROW,ROWLOCK) (pagename,i_users,userlevel,iServers,site_id,ip) 
	VALUES(@page, @iUsers, @userlevel,@iServers,@siteID,dbo.f_frnk_util_IPAddrStr2Int(@vcIP,0))

quote:

---

Originally posted by aiken

Thanks for the advice. The disk is RAID 1, but not RAID 10 (just not enough spindles to do that).

Because the table is only thing on this disk (even its nonclustered index is on a different array), and because it gets 99.99% inserts, isn't it a good thing to have the clustered index sequential, so the heads can just step along one sector at a time? Or would it be preferable to lose the PK and put a nonunique clustered index on something like [pagename], which is going to scatter the writes (and page splits) throughout the table?

I realize that the best answer to all of this is "test it in a development environment and measure the actual results," but I just don't have that luxury right now.

Thanks
-b

---

quote:

---

Originally posted by eyechart

Also, don't put the function in the insert statment. Calculate the value to be inserted outside of the actual insert statement. That way, you don't spend any extra time with a lock in place on the table. Every millisecond counts with an active table like this.

-ec

---

quote:

---

Originally posted by aiken

At this point, I've killed the PK, deleted the identity column, and moved the clustered index to the [datetime],[site_id] fields. I've moved the computations out of the insert,

It definitely made a night-and-day difference in performance. While about 1% of inserts take longer than 50ms, of those the average is about 70ms. Previously, it was about 1% as well, but the average for long-running inserts was about 250ms. Overall, the DB is doing less work, which makes the rest of the app happier as well.

---