Profiler Trace Questions - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Profiler Trace Questions

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-04-07 : 12:47:12

Afternoon,

I was asked to run a trace, like NOW! On a slow app. After I explained if what ever it was wasn't going to take it down I probably would by doing the trace. Well I ran it using the SQLProfilerTuning one. I added cache missed and cache insert. What I saw I wanted to get some feedback back about.

Every time a stored proc ran (same spid) immediately after that I get 1 or more cache misses. My understanding of that is that the proc is being executed but it's not compiling correctly.

One of them takes 640 for the duraction and I don't think I can count that high for cache missed.

Can anyone explain this to me in more detail or give me a better template to use for a slow running app?

Thanks in advance

Laura

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-04-07 : 21:13:11

Can you check the plan for the statements in the stored proc?

Do you know what part of the app is slow, so you can check where it's hitting the DB? Maybe the code is bad, or tables are missing indices, etc.

Sarah Berger MCSD

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-07 : 21:22:50

What's your overall cache hit ratio on the server? Is it just this one proc having trouble? Are there temp tables, IF statements, or cursors in the table? Are there a lot of optional parameters in the WHERE clause? Would you mind posting the query?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-04-08 : 08:21:55

This the query: note the temp tables and the cursor....

------------------------------------------------------
CREATE PROCEDURE dbo.PreparePalletsForScan
------------------------------------------------------
-- purpose: open all pallets associated with a Scan Station
-- called by: ScanOMatic->frmMain.PreparePalletsForScan()
-- ScanOMatic->frmMain.CloseBox()
-- ScanOMatic->frmMain.Pallet()
-- ScanOMatic->ValueScan()
-- calls: CheckScanAccess, GetLocationDefs, GetScanType,
-- CreateNewPalletNum, GetLocationType, GetBoxCount
-- OpenBox, GetBoxTotals
-- status: IN USE
------------------------------------------------------
@userid INTEGER,
@workstationid CHAR(10),
@matrix_id INTEGER,
@btnstate INTEGER
AS
DECLARE @palletid CHAR(15)
DECLARE @box_id SMALLINT
DECLARE @boxcount INTEGER
DECLARE @scan_mode CHAR(10)
DECLARE @qty INTEGER
DECLARE @cost MONEY
DECLARE @retail MONEY
DECLARE @locnum INT
DECLARE @max_boxes SMALLINT
DECLARE @printer CHAR(15)
DECLARE @valued BIT
DECLARE @color CHAR(10)
DECLARE @tier SMALLINT
DECLARE @hex_pair CHAR(8)
DECLARE @scan_type TINYINT
DECLARE @itemqty INTEGER
DECLARE @box_sku INTEGER
DECLARE @box_type TINYINT
DECLARE @trash BIT
DECLARE @loc_type CHAR(4)
DECLARE @trash_color CHAR(10)
DECLARE @dummy CHAR(15)
----------------------------------
DECLARE @boxmode TINYINT
DECLARE @casemode TINYINT
DECLARE @binmode TINYINT
DECLARE @skipit BIT
DECLARE @true BIT
DECLARE @false BIT
----------------------------------
SET @boxmode = 0
SET @casemode = 1
SET @binmode = 2
SET @true = 1
SET @false = 0
----------------------------------
SET @dummy = '*************'
----------------------------------
-- create empty pallet info table
----------------------------------
CREATE TABLE #usrHoldDetail (
[location] [int] NOT NULL,
[palletid] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scan_mode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scan_type] [tinyint] NULL ,
[box_sku] [int] NULL,
[itemqty] [int] NULL,
[qty] [int] NULL,
[max_boxes] [smallint] NULL ,
[cost] [money] NULL ,
[retail] [money] NULL,
[printer] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[valued] [bit] NULL ,
[color] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hex_pair] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loc_type] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tier] [smallint] NULL
) ON [PRIMARY]
----------------------------------
-- create cursor to hold locations for this ScanStation
----------------------------------
DECLARE usrCursorLocation CURSOR FOR
SELECT DISTINCT location
FROM usrWorkstation_Detail
WHERE workstation_id = @workstationid
AND matrix_id = @matrix_id
OPEN usrCursorLocation
FETCH NEXT FROM usrCursorLocation
INTO @locnum
-- cycle through and retrieve pallet ids
WHILE @@FETCH_STATUS = 0
BEGIN
SET @boxcount = 0
SET @itemqty = 0
SET @cost = 0
SET @retail = 0
SET @box_sku = 0
SET @skipit = @false
----------------------------------
-- get scan access for this location
----------------------------------
EXECUTE dbo.CheckScanAccess @userid, @locnum, @btnstate, @skipit OUTPUT
----------------------------------
-- process only if above criteria is met
----------------------------------
IF NOT @skipit = @true
BEGIN
----------------------------------
-- get defaults for this location
----------------------------------
EXECUTE GetLocationDefs @locnum, @max_boxes OUTPUT, @printer OUTPUT, @valued OUTPUT, @color OUTPUT, @tier OUTPUT
----------------------------------
-- get scan mode for this location
----------------------------------
EXECUTE GetScanType @locnum, @scan_mode OUTPUT, @scan_type OUTPUT
----------------------------------
-- get, or create, pallet number associated with this location
----------------------------------
EXECUTE GetPalletNumber @userid, @locnum, @palletid OUTPUT
----------------------------------
-- location type for this location
----------------------------------
EXECUTE GetLocationType @locnum, @loc_type OUTPUT
----------------------------------
-- get box count for this pallet
----------------------------------
EXECUTE GetBoxCount @palletid, @boxcount OUTPUT
----------------------------------
-- grab hex for color display
----------------------------------
SELECT @hex_pair = hex_pair
FROM usrNamedColor
WHERE color = @color
----------------------------------
-- open a box only for BOX (including BIN) mode
----------------------------------
IF ( (@boxmode = @scan_type OR @binmode = @scan_type) )
EXECUTE OpenBox @locnum, @userid, @box_sku OUTPUT
--------------------------------
-- retrieve totals for this box --
--------------------------------
EXECUTE GetBoxTotals @box_sku, @itemqty OUTPUT, @cost OUTPUT, @retail OUTPUT
----------------------------------
-- insert all data into temp tbl
----------------------------------
IF ( @dummy = @palletid )
BEGIN
SET @scan_mode = NULL
SET @itemqty = NULL
SET @boxcount = NULL
SET @cost = NULL
SET @max_boxes = NULL
SET @printer = NULL
SET @color = 'clWhite'
SET @hex_pair = '0xffffff'
SET @loc_type = NULL
END

INSERT INTO #usrHoldDetail (location, palletid, scan_mode, itemqty, qty, cost, retail,
max_boxes, printer, valued, color, hex_pair, box_sku,
scan_type, loc_type, tier)
VALUES (ISNULL(@locnum, 0), ISNULL(@palletid, 0), @scan_mode, @itemqty, @boxcount, @cost, @retail,
@max_boxes, @printer, @valued, @color, @hex_pair, ISNULL(@box_sku, 0),
@scan_type, @loc_type, @tier)

----------------------------------
END
FETCH NEXT FROM usrCursorLocation
INTO @locnum
END
----------------------------------
create_trash_record:
BEGIN
SELECT @trash_color = trash_color
FROM usrOptions

SET @color = SUBSTRING(@trash_color, 3, LEN(@trash_color) - 3)

INSERT INTO #usrHoldDetail
VALUES (0, 'Trash', NULL, NULL, 0, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, @color, @trash_color,'T', NULL)
END
----------------------------------
set_sort_order: -- added 4/8/2004 as per dbrooks
BEGIN
SELECT *
INTO #tmpDetailTbl
FROM #usrHoldDetail
ORDER BY 14, 16
END
----------------------------------
deallocate_cursor:
BEGIN
CLOSE usrCursorLocation
DEALLOCATE usrCursorLocation
END
----------------------------------
get_out_of_here:
BEGIN
SELECT * FROM #tmpDetailTbl
END
------------------------------------------------------

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources