03. April 2010 · Comments Off on Paging Data with a webservice · Categories: T-SQL · Tags: ,
What I found was that it took lots of searching till I found this page of various implementations.
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
Turns out the rowcount one works perfect for my need to access a 6.5 compatibility mode database. (no top functionality).
Here is how I implemented that in the stored procedure (Webservice implementation perhaps later):
CREATE    PROCEDURE spimCounts_FetchRowsByGun_xxx (@gunid char(10), @iPageNum int = 1, @iPerPage int = 10)

AS

begin
set nocount on
DECLARE @ubound INT, @lbound INT, @pages INT, @rows INT
declare @iGunId char(10), @PageNum int, @PerPage int
declare @entrytime datetime
-- Reassign passed parameters to
-- help prevent parameter sniffing performance issues.
select @iGunID = @gunID
select @PageNum = @iPageNum, @PerPage = @iPerPage
-- Find the number of rows to work with and number of pages
SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
timItemCounts_csi WITH (NOLOCK)
where gunid = @gunid
IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum  @pages SET @pagenum = @pages
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)
SELECT
CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows
SET ROWCOUNT @lbound
SELECT
@entrytime = entrytime
FROM
timItemCounts_csi WITH (NOLOCK)
where gunid = @gunid
ORDER BY
entrytime desc
SET ROWCOUNT @perPage
SELECT  entryid, LocationID as 'LocID', ItemID, UPCID, Qty, EntryTime as 'Entry'
FROM timItemCounts_csi WITH (NOLOCK)
WHERE gunid = @gunid
and entrytime
end

What I found was that it took lots of searching till I found this page of various implementations.http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

Turns out the rowcount one works perfect for my need to access a 6.5 compatibility mode database. (no top functionality).
Here is how I implemented that in the stored procedure (Webservice implementation perhaps later):
CREATE    PROCEDURE spimCounts_FetchRowsByGun_xxx (@gunid char(10), @iPageNum int = 1, @iPerPage int = 10)ASbegin set nocount on
DECLARE @ubound INT, @lbound INT, @pages INT, @rows INT  declare @iGunId char(10), @PageNum int, @PerPage int declare @entrytime datetime
-- Reassign passed parameters to  -- help prevent parameter sniffing performance issues. select @iGunID = @gunID select @PageNum = @iPageNum, @PerPage = @iPerPage

-- Find the number of rows to work with and number of pages    SELECT         @rows = COUNT(*),         @pages = COUNT(*) / @perpage     FROM         timItemCounts_csi WITH (NOLOCK) where gunid = @gunid
IF @rows % @perpage != 0 SET @pages = @pages + 1     IF @pagenum  @pages SET @pagenum = @pages      SET @ubound = @perpage * @pagenum      SET @lbound = @ubound - (@perpage - 1)       SELECT         CurrentPage = @pagenum,         TotalPages = @pages,         TotalRows = @rows
SET ROWCOUNT @lbound
SELECT         @entrytime = entrytime    FROM         timItemCounts_csi WITH (NOLOCK)  where gunid = @gunid    ORDER BY  entrytime desc     SET ROWCOUNT @perPage      SELECT  entryid, LocationID as 'LocID', ItemID, UPCID, Qty, EntryTime as 'Entry'    FROM timItemCounts_csi WITH (NOLOCK)     WHERE gunid = @gunid and entrytime end

03. April 2010 · Comments Off on SQL Server Threads and Multiple Files · Categories: SQL Server · Tags: ,
Here is the latest
From this post:
I have been answering questions on two of the most misunderstood ‘SQL Server Urban Legends’ frequently again so I decided to make a post about them.
·         SQL Server Uses One Thread Per Data File
·         A Disk Queue Length greater than 2 indicates an I/O bottleneck
al123 Multiple Filegroups
Hi experts,
I’m new to SQLServer 2000 and would like some advice on filegroups.
Is there any advantage to seperate the filegroups for different type of data.
For example:
Data_1 for tables
Index_1 for Indexes
Audit_1 for Audit tables
The files for these filegroups would be placed on RAID Disk.
Eric C. (Eric Christensen, SQL Server Storage Engine)
No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.
—————–
If your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let’s say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.
Essentially, the more files that a large table is divided into, the greater the potential performance. Of course there is a point where the additional threads aren’t of much use when you max out the server’s I/O. But up until you do max out the I/O, additional threads (and files) should increase performance. [7.0, 2000] Updated 9-19-2005
—————–
Configuring a server with either 4 or 8 tempdb files may by itself cause performance problems (due to the overhead of SQL having to manage too many files), so the recommendation should be changed to start with just 1, and monitor for tempdb contention. ONLY if you see contention then add more files until the contention is resolved. However it’s worth noting that just adding more files won’t always avoid tempdb performance issues, as it’s entirely possible that the issue is with a slow I/O subsystem. Adding more files ONLY helps resolve contention for the schema lock when creating new objects, which in tempdb can happen at a very high rate.  However not all applications make use of tempdb. It’s possible that specific application may make no use of tempdb. In that case creating multiple tempdb files is a big overhead and a waste of time.
—————-
From: Al – view profile
Date: Wed, Jan 23 2002 5:01 pm
Groups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
BTW, I came across this in SQL 2000 BOL.
—————————————————————————-
Placing Tables on Filegroups
If the computer has multiple processors, Microsoft® SQL Server 2000 can
perform parallel scans of the data. << Multiple parallel scans can be
executed for a single table if the filegroup of the table contains multiple
files.>> Whenever a table is accessed sequentially, a separate thread is
created to read each file in parallel.  << For example, a full scan of a
table created on a filegroup comprising of four files will use four separate
threads to read the data in parallel. Therefore, creating more files per
filegroup can help increase performance because a separate thread is used to
scan each file in parallel. >>
From: Andrew J. Kelly – view profile
Date: Wed, Jan 23 2002 7:37 pm
Groups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
This is an error in BOL2000.  I have already reported it and it is confirmed
that it will state otherwise for the next revision.  It is misleading but I
have confirmed at the source that 2000 does not require multiple files to
spawn multiple threads.
Andrew J. Kelly,  SQL Server MVP
TargitInteractive
Please join me at PASS North America in Denver – the first and only user
conference dedicated to SQL Server.
http://www.sqlpass.org/events/denverjan/index.cfm
“Al” <nos…@nospam.com> wrote in message

Here is the latestFrom this post:http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspxI have been answering questions on two of the most misunderstood ‘SQL Server Urban Legends’ frequently again so I decided to make a post about them.·         SQL Server Uses One Thread Per Data File·         A Disk Queue Length greater than 2 indicates an I/O bottleneck

Here is the older stuffhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=83430&SiteID=1
al123 Multiple Filegroups Hi experts,I’m new to SQLServer 2000 and would like some advice on filegroups.Is there any advantage to seperate the filegroups for different type of data.For example:
Data_1 for tablesIndex_1 for IndexesAudit_1 for Audit tables
The files for these filegroups would be placed on RAID Disk.
Eric C. (Eric Christensen, SQL Server Storage Engine)No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.
—————–http://www.sql-server-performance.com/filegroups.aspIf your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let’s say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.
Essentially, the more files that a large table is divided into, the greater the potential performance. Of course there is a point where the additional threads aren’t of much use when you max out the server’s I/O. But up until you do max out the I/O, additional threads (and files) should increase performance. [7.0, 2000] Updated 9-19-2005
—————–http://sqljunkies.com/WebLog/odds_and_ends/archive/2006/06/21/21985.aspx
Configuring a server with either 4 or 8 tempdb files may by itself cause performance problems (due to the overhead of SQL having to manage too many files), so the recommendation should be changed to start with just 1, and monitor for tempdb contention. ONLY if you see contention then add more files until the contention is resolved. However it’s worth noting that just adding more files won’t always avoid tempdb performance issues, as it’s entirely possible that the issue is with a slow I/O subsystem. Adding more files ONLY helps resolve contention for the schema lock when creating new objects, which in tempdb can happen at a very high rate.  However not all applications make use of tempdb. It’s possible that specific application may make no use of tempdb. In that case creating multiple tempdb files is a big overhead and a waste of time.
—————-From: Al – view profileDate: Wed, Jan 23 2002 5:01 pmGroups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
BTW, I came across this in SQL 2000 BOL.—————————————————————————-Placing Tables on Filegroups
If the computer has multiple processors, Microsoft® SQL Server 2000 canperform parallel scans of the data. << Multiple parallel scans can beexecuted for a single table if the filegroup of the table contains multiplefiles.>> Whenever a table is accessed sequentially, a separate thread iscreated to read each file in parallel.  << For example, a full scan of atable created on a filegroup comprising of four files will use four separatethreads to read the data in parallel. Therefore, creating more files perfilegroup can help increase performance because a separate thread is used toscan each file in parallel. >>
From: Andrew J. Kelly – view profileDate: Wed, Jan 23 2002 7:37 pmGroups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
This is an error in BOL2000.  I have already reported it and it is confirmedthat it will state otherwise for the next revision.  It is misleading but Ihave confirmed at the source that 2000 does not require multiple files tospawn multiple threads.
— Andrew J. Kelly,  SQL Server MVPTargitInteractive
Please join me at PASS North America in Denver – the first and only userconference dedicated to SQL Server.http://www.sqlpass.org/events/denverjan/index.cfm
“Al” <nos…@nospam.com> wrote in message