MS SQL Server :: Scan Density

Scan Density < 50%

Aug 14, 2001

I am having problems w/ the indexes on a particular table. Currently the scan density if 25%. I have ran the dbcc dbreindex and the scan density does not improve. I have manually dropped and recreated all of the indexes on the table (there are only two) and still no help.

I have a clustered Primary Key index on my part_number and project_number fields and another index on the project_number field. If I drop the second index and only have my primary key index, it still doesn't help. The fill factor was originally set to 90% and I changed this to 30%, 20%, 10%, 0% and the best scan density I could acheive was 50%.

The data in this table is not updated that often, on average about 15 records a day are updated, and there are only 107 records. This is a small table, but there is a possibility for it to grow rapidly and I want to be prepared now.

Does anyone have any ideas or suggestions? I've tried everything I can think of with no luck.

TIA,

Chris

View 7 Replies View Related

How To Impove Scan Density

Aug 27, 2001

Case
I run dbcc showcontig
Result
DBCC SHOWCONTIG scanning 'rpRateCode' table...
Table: 'rpRateCode' (213627854); index ID: 1, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2] !!!!!!!
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 3088.5
- Avg. Page Density (full).....................: 61.84%

Bellow is table structure

CREATE TABLE [dbo].[rpRateCode] (
[ID_RateCode] [ID] NOT NULL ,
[ID_ScheduleOfPresence] [ID] NULL ,
[ID_ScheduleOfBilling] [ID] NULL ,
[IsExtensible] [bit] NOT NULL ,
[IsFixedRate] [Boolean] NOT NULL ,
[ID_RateCodeFollow] [ID] NULL ,
[ID_RateDef] [ID] NULL ,
[ID_MarketSegment] [ID] NOT NULL ,
[ID_HskLevelPattern] [ID] NULL ,
[ComplimentaryType] [AType] NULL ,
[BillingType] [AType] NULL ,
[Discriminator] [Discriminator] NOT NULL ,
[PrintRateOnConfirm] [Boolean] NOT NULL ,
[PrintRateOnArrivalList] [Boolean] NOT NULL ,
[PrintRateOnRegCard] [Boolean] NOT NULL ,
[MinNights] [NbOfNights] NULL ,
[MaxNights] [NbOfNights] NULL ,
[IsLimited] [Boolean] NULL ,
[presencetype] [tinyint] NULL ,
[RateNote] [varchar] (60) NULL ,
[IsAgeCatDiffRoom] [Boolean] NULL ,
[ID_Currency] [ID] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rpRateCode] WITH NOCHECK ADD
CONSTRAINT [PK__rpRateCode__2EBAFAC0] PRIMARY KEY CLUSTERED
(
[ID_RateCode]
) ON [PRIMARY]
GO

My question how to increase Scan Density for this table if CLUSTERED index exist?

View 1 Replies View Related

Scan Density Question

Dec 16, 2005

Can someone explain why i can drop and recreate indexes on say 2 tables and they had a scan densitiy of say 15% and 10%.

After i recreate the indexes on both tables one comes back with a scan density of 100% and the other only 25%.

What would cause this table to only go to 25% and not 100% like i want it to be?

Thanks for your help :)

View 13 Replies View Related

Scan Density Of Nonclustered Indexes

Oct 23, 1999

A few days after droping and recreating noclustered indexes on a large table, scan density of most indexes falls below 20%. Only a few number of rows has deleted. Update operation didn't update these keys and there was a lot of inserts. Could anyone explain why are indexes fragmented so much?

View 2 Replies View Related

What Is The Difference Between Clustered Index Scan And Table Scan

Jan 29, 2006

How can I improve performance of my search if I am looking in a table with more than ten million rows with a "like query"?

Does putting an index mean only telling the computer to start in a particular order?

if I index all the coloums does my search get faster ?

how can I decide on an indexing strategy?

View 7 Replies View Related

Table Scan On Inserted And Deleted Tables?

Apr 30, 2001

Hello,

I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though.

Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell.

Thanks for any help!
-Tom.

UPDATE H_MyTable
SET HIST_END_DT = @tran_date
FROM H_MyTable his
INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and
(his.PrimaryKey2 = del.PrimaryKey2)
INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and
(his.PrimaryKey2 = ins.PrimaryKey2)
WHERE (his.HIST_END_DT is null)
and ((IsNull(del.PrimaryKey1, -918273645) <>
IsNull(ins.PrimaryKey1, -918273645)) or
(IsNull(del.PrimaryKey2, -918273645) <>
IsNull(ins.PrimaryKey2, -918273645)) or
(IsNull(del.Column3, -918273645) <>
IsNull(ins.Column3, -918273645)))

View 1 Replies View Related

How To I Enter A Small Square Or A Small Circle In A NVARCHAR Field?

Dec 25, 2006

I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.

Thanks

View 4 Replies View Related

Index Seek, Index Scan, Table Scan

Oct 4, 2007

Hey,

what is the difference between Table Scan und Index Scan?

I find no difitions in the internet

Finchen

View 5 Replies View Related

What Is Table Scan, Index Scan And Index Seek??

Sep 21, 2007

Hi,
I want to know wht is a

TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????

View 5 Replies View Related

Keyword Density/Count

Apr 16, 2007

Hi All,let me try to explain what I'm trying to accomplish - I really hope someonecan help.I have a table (tblArticles) which has the following:vcrKeywords varchar(2000)txtBody text(8000)vcrType varchar(128)and this is a sample of the datavcrKeyWords || txtBody || vcrTypekey1,key2,key4,key7,key9 || <snipped body|| Site5 Newskey1,key3,key6,key8,key9 || <snipped body|| Site5 Newskey1,key3,key4,key5,key9 || <snipped body|| Site5 Newskey1,key2,key5,key7,key8 || <snipped body|| Site5 NewsWhat I'm trying to accomplish is to return a keyword count based on thecontent of vcrKeywords (i.e. each comma seperated entry as a count.My SQL statement originally was :SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticleswhere vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESCHowever, this simply matches the entire vcrKeyword Column and not each commaseperated value.Is there a way in SQL that I can achieve this or do I need to use some kindof scripting language to accomplish it...?Regards,Carl.

View 6 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

Page Density/dbcc Showcontig

Dec 5, 2000

I ran a DBCC Showcontig (tableid) and it tell me that one of my tables is only putting 0.19% data on each page. How can I control this for more data on a page. It tells me that I have 8078.8 bytes free per page.

View 1 Replies View Related

Getting Density Of Values Across Years And Months

Aug 29, 2007

Hi All,

I have the following table "Project"

-------------------------------------------------------------------------------------------
ID Name Start Date End Date
--------------------------------------------------------------------------------------------
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
...
015 Project 15 2-20-2006 3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.

Output 1:

------------------------------------------------------------------------------------------------------------------------------------------------
Year Projects No. Of Projects Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------

(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following

Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year Start Date Project Name
-----------------------------------------------------------------------------------------
2003

February 2 Project 1
January 24 Project 2
2005
April 4 Project 3
2006
May 5 Project 4
February 20 Project 15
-------------------------------------------------------------------------------------------

I am very much in need of Output 1. Could someone help me,

View 5 Replies View Related

Compensate For Keyword Density In Full Text Search

Apr 8, 2008

Hi,

When using FREETEXTTABLE the RANK returned seems to be partially based on keyword density. Has anyone come across a method of compensating for this keyword density so that good matches found in a lot of text and a small amount of text return the same RANK?

For an example look at the site I'm working on when someone searches for "whale watching" - http://www.yougodo.com/Search.aspx?ks=whale+watching - you can see that we are showing poor quality results at the top as our first sort order is based on RANK.

If we could remove the keyword density factor from RANK then this would allow second order sorting criteria to come in to play and move the more valuable results to the top.

Any help, pointers, advice would be greatly appreciated,
Gavin.

www.gavinharriss.com

View 1 Replies View Related

Scan

Oct 8, 2007

I write a work about database, and I want to write the diffrence between Table scan and Index scan. When is the index scan effectient? Use the database a Table scan when there is an index on the table or always a index scan when the index is nonselective? Can be a Table Scan effecient than a index scan?

View 1 Replies View Related

How To Get Rid Of Table Scan

May 26, 2000

Can anyone tell me how to get rid of the Table Scan(1 million rows)being performed on the
The last line, option (loop loin) stopped table scanning the B.ss_manifest and started using the index, I'd like both tables to use the index.
This is the argument I get from execution plan under the table scan.
Object ((D4000).(dbo).(shipstop)as (A))

update drivers set dr_miles_run = case when D1.miles > 0 then d1.miles else 0 end
from (select mf_dr_nbr, sum( case when A.ss_end_dt < '05/17/00' then
( cast((datediff(day, '05/17/00' , B.ss_end_dt ) + 1) as float) /
cast( (datediff(day, A.ss_end_dt , B.ss_end_dt ) + 1) as float) * mf_ld_miles)
else mf_ld_miles end) as miles
from manifest, shipstop A, shipstop B
where mf_manifest_nbr = A.ss_manifest_nbr
and mf_manifest_nbr = B.ss_manifest_nbr
and A.ss_stop_type in ('OR','SA')
and B.ss_stop_type in ('DT','RD')
and ((A.ss_end_dt >= '05/17/00 00:00' and A.ss_end_dt < '05/24/00 00:00')
OR ((B.ss_end_dt >= '05/17/00 00:00' and B.ss_end_dt < '05/24/00 00:00'))) and mf_status > 3 group by mf_dr_nbr ) as D1
where Drivers.dr_driver_nbr = D1.mf_dr_nbr
option (loop join)

Thanks for any ideas!

View 3 Replies View Related

Count Scan

Aug 21, 2006

Hi,

I have a simple temp table which looks like this:

CREATE TABLE #t7e07c01fa80143ff84cb14a2307809f7
(
[AUTOID_TEMP] [int] IDENTITY (1, 1) NOT NULL,
...
)
ON [PRIMARY]

ALTER TABLE #t7e07c01fa80143ff84cb14a2307809f7 ADD
CONSTRAINT [PK_t7e07c01fa80143ff84cb14a2307809f7] PRIMARY KEY NONCLUSTERED
(
[AUTOID_TEMP]
)
ON [PRIMARY]

Insert Into #t7e07c01fa80143ff84cb14a2307809f7
Selet columns... from t....

-- get total records
Declare @TotalCount as int
select @TotalCount = count(AUTOID_TEMP) from #t7e07c01fa80143ff84cb14a2307809f7

Now the above last statement does a index scan. I am new to indexing/tuning and was wondering if its normal - if so why and can I somehow enhance this?

Thanks

View 5 Replies View Related

Scan Directory

Nov 1, 2007

Hey guys,

I have a procedure that runs everyday which takes the IIS log file from the previous day and imports it and calcs values. I would like to change this from taking the previous day to scanning all the file names in the folder and comparing them to a database to see if they have already been scanned before. I already have a table called DatesScanned which lists the filename and scandate.

IE:

D:Logsfile1.log
D:Logsfile2.log
D:Logsfile3.log
D:Logsfile4.log

The 4 file names will be scanned and compared to DatesScanned to see which has been imported already.

Can anyone point me to a resource or information on how I would get the directory listing of a folder so I could compare it to the DatesScanned table.

Thanks a bunch!

Jeff

View 5 Replies View Related

Scan Count

Jul 23, 2005

Hi,Can someone please explain the following1. Meaning of scan count as reported when "statistics io" is turned onprior to running a query.2. In which situations could you have an identical database runningon two diferent servers , with identical database serverconfigurations, running identical queries, with identical query plan,report large discrepency in the scan count . This is one of thepossibilities we are looking at in terms of the reason why one serverruns the job in 12 hours and the other in 24 hours.Server 1--------Table 'TRANS_HISTORY'. Scan count 216, logical reads 897093, physicalreads 44, read-ahead reads 900599.SQL Server Execution Times:CPU time = 27766 ms, elapsed time = 46850 ms.UNIT_NUMBERACCOUNT_TYPETRANSACTION_TYPEServer 2--------Table 'TRANS_HISTORY'. Scan count 491, logical reads 952759, physicalreads 51, read-ahead reads 954414.SQL Server Execution Times:CPU time = 31563 ms, elapsed time = 145595 ms.UNIT_NUMBERACCOUNT_TYPETRANSACTION_TYPEI thank you in advance for your assistance.Puvendran

View 1 Replies View Related

Table Scan

Jul 9, 2007

Does SQL Server allow a table scan to be used when querying a table that has a clustered-index? If yes, could someone please show me the syntax? I have tried with (index(0)) but this appararently means a clustered index scan when there is a clustered index on the table to be queried.

Or does clustered index scan mean the same thing as a table scan when the table has a clustered index? Confused.

Thanks

View 1 Replies View Related

Table Scan

Nov 28, 2007

Friends,

I am using this query in a table with 29,000 records having non-clustered index on emp_id

select * from employee

where emp_id between 100 and 1000

and gender = 'M'

order by emp_id

execution plan shows a table scan in table employee. Any reason?

select * from employee with (index = emp_id)

where emp_id between 100 and 1000

order by emp_id

ofcourse this uses index seek

regards,
Priw

View 15 Replies View Related

Invalid Log Scan

May 21, 2007

I do not understand it but by connection to my database seems to keep crapping out. I get the below error and my app appears to be unable to access the connection. Then a couple restarts later it tells me that my login under my Windows User failed . The database is there and besides maybe a couple records longer there isnt anything corrupt about the data, and in fact, if I restart the entire PC most times it will allow me to connect again. I can even still access the data tables through the C# editor. Any idea why this error is being thrown?

Thx

Code SnippetSystem.Data.SqlClient.SqlException was unhandled
Message="A system assertion check has failed. Check the SQL Server error log for details
The log scan number (35:460:2) passed to log scan in database 'F:\PDLOGSHEET\PDLOGSHEET\BIN\DEBUG\DATABASE1.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'F:\PDLOGSHEET\PDLOGSHEET\BIN\DEBUG\DATABASE1.MDF' (database ID 26) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Cannot open user default database. Login failed.
Login failed for user 'GC\cbcottier'.
Location: logscan.cpp:1925Expression: UtilDbccIsInsideDbcc ()SPID: 51Process ID: 3640
Location: logscan.cpp:1925Expression: UtilDbccIsInsideDbcc ()SPID: 51Process ID: 3640"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=20
LineNumber=65536
Number=3624
Procedure=""
Server="\\.\pipe\E74940B1-0BD7-42\tsql\query"
State=1
StackTrace:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at PDLogSheet.Database1DataSetTableAdapters.ReportTitlesTableAdapter.Fill(ReportTitlesDataTable dataTable) in F:PDLogSheetPDLogSheetDatabase1DataSet.Designer.cs:line 4362
at PDLogSheet.frmLogSheet.frmLogSheet_Load(Object sender, EventArgs e) in F:PDLogSheetPDLogSheetfrmLogSheet.cs:line 42
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at PDLogSheet.Program.Main() in F:PDLogSheetPDLogSheetProgram.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

View 5 Replies View Related

Tabel Scan

Oct 29, 2007

Hi All,

When an end user view a report using report manager or a custom build web site, would query of the report scan the tables in original data base? In this case it is a OLTP data base.

Thanks

View 4 Replies View Related

SUM Uses Table Scan But Not Index

Jan 16, 2002

Hi I'm issuing a SELECT on a field with the SUM on SQL Server 7. I have an index on the field in the WHERE clause but upon analysis, the Query Optimizer always uses a Full Table Scan. Can anyone explain why and is there a way to use the index.

HEre's the structure:
SELECT SUM(colA)
FROM TABLE tblB
GROUP BY colC

An index exists on column colC.

Thanks

View 1 Replies View Related

Index Seek Vs Scan

Nov 4, 2002

I have created a nolock view off a table to prevent locks. I have users coming in through MS Access that have switched their queries to run against the views. Now we are noticing that queries that used to run as a clustered index seek against the table are running as a clustered index scan against the table and performance in the queries has dropped.

Is there any way that the same query that hits the view instead of the table can be made to run faster or at least use the index seek?

Thanks,

Steve

View 4 Replies View Related

Is My Table Scan Unavoidable?

Jun 10, 2004

Hi guys-n-gals...

I have a table that contains the following:
PortfolioID(int), EndDate(smalldatetime), Begdate(smalldatetime), WklyCloseIndex (float)

It has a primary key which is PortfolioID/BegDate/EndDate

I need to create a table that summarizes, by date range, the weeklycloseindex of several portfolios, like this:

BegDate EndDate Portfolio2 Portfolio67 Portfolio11 Portfolio90
05/28/2004 06/04/2004 xx.xx xx.xx xx.xx xx.xx
05/21/2004 05/28/2004 xx.xx xx.xx xx.xx xx.xx

So I wrote a function...

This function results in a table scan *GASP!!!!* (at least that's what the 'splain plan tells me when I run it in SQL Analyzer). Before I made it into the function, when I was testing the code in SQL Analyzer, it resulted NOT in a table scan, but rather a series of nested loops (the joins) and clustered index seeks...resulting in about 1/3 the total cost of the function.

I suspected originally that it was the TOP/ORDER BY that the function insisted upon, but even if I remove those, still get a table scan.

Wassup? Why does the function turn my cool lil' self-join into a table scan? Whut am I missing? Any thoughts? Disgusted Derisions? Hurled Insults? Bring it on!!! (please! ;) )

My predecessor did this in a similar project using a separate cursor for each portfolio by date, then looped through the dates, pulling in the per-portfolio index value and building the output table. I would rather avoid the cursors if I can.

View 4 Replies View Related

Clustered Index Scan

Apr 8, 2008

I am running a SELECT on a table. This READ operation ends up going through Clustered Index Scan. I want to know whether Clustered Index Scan , blocks other concurrent transactions trying to INSERT into this table? Does Clustered Index Scan locks the entire clustered index?

View 3 Replies View Related

Logical Scan Fragmentation

Aug 16, 2007

I have a table with 50% Logical Scan Fragmentation. [ according to Dbcc Showcontig (myTable) ]
Why after running DBCC INDEXDEFRAG (myDB,myTable) does it still sit at 50%.
Why isn't it lower?

View 7 Replies View Related

Scan Path Of Image

Feb 25, 2008

Hi all,
I have a scanner that dump the images into a directory. Because of thousand images dump per day, I want to moved the images into another directory each day as nightly schedule. However I like the path of the images moved stored into the database, to retrieve later. Have anyone design something like this? Right now, I get the file name from the text files, import to the database and update the path directory. I was hoping to get away from this...and do this as a jobs.

thanks

View 2 Replies View Related

Table-Scan Speed

Oct 2, 2007

I have the following scenario (with 2K5 Express)

I have a table with 160.000 records.

I have to retrieve 10.000 - 40.000 records by their ids (<3seconds would be sufficent)
I first used single requests, then one single command as batch (simply joined the single commands into one string).
But that was very slow (30 seconds if cached). so I created one big statement

select myfields
from mytable where id in(1,2,..,35000)

if everything is cached the speed is fine (<1second), but if I retrieve the data for the first time
it takes 15-30 seconds, that's a bit too slowish.

the total database size is 100MB - so a file scan should be faster, I thought at least

so HERE is the problem why I post this

to force the table scan I used
Select myFields From mytable With (Index(0) ...

that took > 3 minutes

I tested the raw IO-time, that was 2,5-3 seconds with the db-file

has SQL Server a problem with the 35.000 items in the condition?
(If it loopes 35.000 x 160.000 times instead of using a hash for the items that would explain the slow speed)

or another reason:
is table scanning always much slower then the raw io operations?

the id-index is not grouped and ( I really don't know why) not marked as primary key,
but that shouldn't have any impact on a file-scan, I guess.

Has anyone faced (and solved) a similiar problem?

View 3 Replies View Related

Query Logical, Scan Reads?

Dec 22, 2000

Hi Everybody,

One of my friend asked me "How do we reduce the query logical, scan reads
in SQL Server?".

I really don't know, how to answer him.

Can anybody explain me regarding this.

thanks,
Srini

View 2 Replies View Related

High Extent Scan Fragmentation

Oct 25, 2002

Can anyone let me now why the extent scan fragmentation is very high.
I do have a clustered index on this table .
The fill factor is 0 and this table has high inserts as it is used to maintain history.
Rebuilding the Indexes did not help.

DBCC SHOWCONTIG scanning 'ACCOUNTS' table...
Table: 'ACCOUNTS'(1061578820); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 728157
- Extents Scanned..............................: 91759
- Extent Switches..............................: 93305
- Avg. Pages per Extent........................: 7.9
- Scan Density .......: 97.55% [91020:93306]
- Logical Scan Fragmentation ..................: 0.33%
- Extent Scan Fragmentation ...................: 99.99%
- Avg. Bytes Free per Page.....................: 76.6
- Avg. Page Density (full).....................: 99.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator

Thanks in advance,
Shades[B]Extent Scan Fragmentation

View 2 Replies View Related

Scan Density - Small Tables