February 24, 2009 at 8:20 am
We have a website that wherever user hits we insert a record in database in following Table. A sample is here:
There are almost 1,500,000 records into it- just a rough idea.
CREATE TABLE [dbo].[tbl_Tracking_mstr](
[session_code] [varchar](50) NOT NULL,
[Login_Id] [varchar](100) NOT NULL,
[Domain_Name] [varchar](50) NOT NULL,
[Login_Name] [varchar](256) NULL,
[Login_Type] [numeric](18, 0) NOT NULL,
[Guest_Id] [varchar](50) NULL,
[Start_DateTime] [datetime] NULL CONSTRAINT [DF_tbl_Tracking_mstr_Start_DateTime] DEFAULT (getdate()),
[End_DateTime] [datetime] NULL,
[Referer_Url] [varchar](1000) NULL,
[Browser_Info] [varchar](1000) NULL,
[IP_Address] [varchar](20) NULL,
[Administration_Id] [numeric](18, 0) NULL,
[Administration] [varchar](100) NULL,
[Region_Id] [numeric](18, 0) NULL,
[Region] [varchar](100) NULL,
[Facility_Id] [numeric](18, 0) NULL,
[Facility] [varchar](100) NULL,
[Subnet_Id] [numeric](18, 0) NULL,
[Subnet] [varchar](100) NULL,
[tracking_end_datetime] [datetime] NULL CONSTRAINT [DF__tbl_track__track__77809FC6] DEFAULT (getdate()),
[IsPopUpBlockerEnabled] [bit] NULL,
[Session_End_Agent] [varchar](50) NULL,
[session_id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [Pk_Session_ID] PRIMARY KEY CLUSTERED
(
[session_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
we are using following sp to insert user info in this table as :
CREATE PROC sp_Tracking_StartSession
(
@session_code varchar(50),
@Login_id varchar(100), @Login_name varchar(256), @Login_type numeric(9),
@Referer_url varchar(1000), @Browser_info varchar(1000), @IP_address varchar(20), @Administration_id numeric (9),
@administration varchar(100), @Region_id numeric (9), @region varchar(100), @Facility_id numeric (9),
@facility varchar(100), @Subnet_id numeric (9), @subnet varchar(100),
@DomainName varchar(50) , @GuestId varchar(50),
@HTTP_Accept varchar(1000),
--@IsPopUpBlockerEnabled bit,
@Session_id INT OUTPUT
)
AS
BEGIN
INSERT INTO tbl_Tracking_mstr(
session_code,
Login_Id, Domain_Name, Login_Name, Login_Type, Guest_Id,
Start_DateTime, Referer_URL, Browser_Info, IP_Address, Administration_Id, Administration, Region_Id,
Region, Facility_Id, Facility, Subnet_Id, Subnet)
VALUES(@session_code,
@Login_id, @DomainName, @Login_name, @Login_type, @GuestId,
GetDate(), @Referer_url, @Browser_info,
@IP_address, @Administration_id, @administration, @Region_id, @region, @Facility_id, @facility, @Subnet_id, @subnet)
SET @Session_id = @@IDENTITY
END
The problem is we used a tool to verify which query is taking time, so it gives us above query that it is taking time and it should be tunned. I don't have idea how further i may tune it?
Please help how to tune this...
If anything else is required, let me know.
Shamshad Ali.
February 24, 2009 at 8:34 am
Also the Update on this table is noticed as slowest running SP:
Please have a look at this and help how can i improve perforamnce:
CREATE PROC sp_Tracking_UpdateSession
(
@Session_id INT, @Login_id varchar(100), @Login_name varchar(256), @Login_type numeric(9),
@DomainName varchar(50) , @GuestId varchar(50), @IsPopUpBlockerEnabled bit,@Referer_Url varchar(1000)
)
AS
BEGIN
UPDATE tbl_Tracking_mstr
SET Login_Id = @Login_Id,
Login_name = @Login_name,
Login_type = @Login_type,
Domain_Name = @DomainName,
Guest_Id = @GuestId,
IsPopUpBlockerEnabled = Case When @IsPopUpBlockerEnabled >= 0 Then @IsPopUpBlockerEnabled Else IsPopUpBlockerEnabled End,
Referer_Url = @Referer_Url
WHERE Session_Id = @Session_Id
END
February 24, 2009 at 8:47 am
I would expect that proc to run quite rapidly. It's not doing a lot of work. Just a simple insert.
How fragmented is the table's clustered index? How about other indexes? Does it have any?
Any triggers on the table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2009 at 9:14 am
Yeah GSquared has it nailed. Nothing in there should be running slow from a pure code structure stand-point. It's likely some other cause. Have you run a server-side trace to capture performance metrics over time? If not, I'd suggest that. It'll at least give you an idea if it's an occasional occurence, a steadily increasing time... whatever is happening, you'll have the information at hand.
I'd also recommend looking at the waits & queues following this white paper from MS: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2009 at 12:26 pm
I believe you maybe a victim of "blocking" please ensure table scanning is not happening.
I don't see any other indexes on that table posted ( are there any ?)
* Noel
February 24, 2009 at 3:59 pm
I agree; one single, keyed INSERT should not take that long.
(As an aside, though, that is a fairly long row (in bytes). If you do not update rows after they are inserted, be sure to set the fillfactor for the table to 100, since you will never insert rows between already existing rows. If the fillfactor is the default 10%, and you reorg the table, you will increase the # of pages you need by at least 10%.)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2009 at 8:07 am
There is no any trigger and but one non clustered index, having Administration_ID, administration, Regiond_Id, Region, Facility_ID and Facility all are included in one non clustered index with no fillfactor selected. I think that means default 10%. Am I right?
Also for Clustered index [session_ID auto increment] there is no no fillfactor, fill factor check box is disable. would it be the problem and cause for slow performance?
Also we update few fields per session_id in this table, one is when user closes his session, End-time is updated for that session record. so what you guys suggest now in such situation how it would be configured table ?
If anything else is required, let me know.
Thanks.
Shamshad Ali
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply