SQL 2012 :: Locking Behavior On Indexed Views

Jul 13, 2014

I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:

CREATE TABLE [Person].[Person](
[BusinessEntityID] int NOT NULL,
[FirstName] varchar(20) NOT NULL,
[MiddleName] varchar(20) NULL,

[code]....

Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:

SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) AS [SUMQuantity]
FROM [Person].[Person]
GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]

The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:

SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'

The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view. I'll then experience more locks / blocks, because now the queries are seperated on two objects.

View 4 Replies


ADVERTISEMENT

Don't Understand The Following Locking Behavior

Jul 23, 2005

can't figure out why the following locking scenario works the way itdoes:spid 1:start transactionselect a row from table T1 with updlockresult: see an Update lock for the row and index keyspid 2:query for the same row as in session 1result: the query succeedsspid 3:do same as spid 1result: blocks trying to get Update lock on the index key for rowNow I do the query again in spid 2 and it blocks trying to get a Sharedlock on the index key, and it's waiting for spid3. why was it able toget a shared lock on a index key that had an Update lock at first butthen can't get the same shared lock when somebody else is also tryingto get an update lock?

View 7 Replies View Related

Locking Behavior Causing Concurrency Issues

Jun 8, 2007

Hi,



We have a batch job that runs for about 45 min and reference couple of
tables in queries but on one of the table DD (dummy name) we want to
take a shared lock so that other sessions can read the table but no
session should be able to write to table, so at the beginning of
transaction/batch job we take a SHARED level table lock on this table.
Now the locking behavior is strange, If someone want to read the table
(IS on table DD) they are working fine but as soon as one of the DML
wants to modify the table and get blocked to get IX lock on DD other
sessions who just wants to read the table and need to get the IS lock
gets blocked so system is kind of hung after that. I tried the same
scenario on SQL Server 2005 and it works fine so this looks like kind
of bug in SQL Server 2000 unless Microsoft consider this as "AS
Designed" locking strategy. We can the following queries to reproduce
the issue:

>From QA window1 run the following code:

create table dd (dd int primary key, dd1 int,dd2 int)


set nocount on
declare @i int
begin
set @i=1
while (@i < 10000)
begin
insert into dd values(@i,@i+10000,@i+100000)
set @i=@i+1
end
end


Now we have a table with about 26 pages and 10k rows.


Now we run the following code from same window:
Begin Tran
select 1 from dd with(tablock,holdlock)
Don't commit at this point


And we will see the following using sp_lock:
57 7 850818093 0 TAB S GRANT





Now on Window2 in QA run the following code:
begin tran
insert into dd values(10000,10001,100001)


And we will see that session in window2 is waiting for IX lock:
56 7 850818093 0 TAB IX WAIT
57 7 850818093 0 TAB S GRANT





Now we run the following code from Windows3, we are just reading the
table and it still gets blocked:
select * from dd where dd=99


56 7 850818093 0 TAB IX WAIT
57 7 850818093 0 TAB S GRANT
66 7 850818093 0 TAB IS WAIT





Since this is fixed in SQL Server 2005 we are wondering if this is
known issue and there is any hotfix available or this is considered by
Microsoft as "as designed?
What is the other alternative to block writers on this table and allow
readers when this batch job is running?

Thanks
--Harvinder

View 1 Replies View Related

Indexed Views

Jan 28, 2005

Hello,
I'm currently performance tuning a table with 100 million rows in it
(about 18 GB of data) and would like to know if -

1. Is the table too large to be performance tuned. Is it better to just
redesign the schema ?
2. Can using techniques as indexed views really help me with tuning such a table.
3. How long would it take to create a clustered, non clustered index on
a varchar column (for instance) on a table with 100 million rows ?
(i know this is a function of hardware as well - let's assume i'm using
afairly maxed out DL 360 - i.e. dual processor with 4 GB of memory)

Thank you very much
Alan

View 14 Replies View Related

Indexed Views

Mar 9, 2005

I am looking for a little insight. I am using an SQL Server database created by a third party vendor. There are certain columns in a given table that I query for quite often. To speed things up, I created an indexed view.

Now I can no longer insert into the base table. Attempting an insert causes a SQL error stating that the system properties ARITHABORT and NUMERIC_ROUNDABORT are incorrect. If I remove the index from my view, the inserts work just fine.

Can somebody provide some insight as to why this happens and how I might be able to correct it (keep in mind that the DB was setup by a third party, so I cannot change too much of the underlying setup without possibly compromising their functionality).

View 7 Replies View Related

Indexed Views

Apr 3, 2008

Hi,

I am trying to create an indexed view, but because I am using a MAX function, I get the error

Cannot create index on view "dbo.View" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

Am totally stuck on how I can replace the MAX function.

Any help would be appreciated.


SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

CREATE VIEW [dbo].[View]
WITH SCHEMABINDING
AS

SELECT TOP 100 PERCENT MAX(js_id) AS job_event, job_id
FROM dbo.JobEvent
GROUP BY job_id
ORDER BY job_event
GO

CREATE UNIQUE CLUSTERED INDEX IX_VMaxJobEvent ON View (job_id)



Thanks

View 2 Replies View Related

Regarding Indexed Views

Aug 6, 2007

Dear experts,
I've been working for an ERP solutions, company, as a DBA....

we have around 1200 tables as wellas 650 views.....

we are not using clustered index on views.....
using the clustered index will boost the performance? and the ERP is web based application. so that modifications will be done on a regular basis....
is it good thing to implement clustered indexes on these views....

please guide me in this regard


thank you verymuch

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

Indexed Views Can Not Have Self-join !!!

Feb 10, 2006

is there any work around this?

View 1 Replies View Related

Indexed Views Tbl Requirements

Jan 17, 2003

I had some issues yesterday with the fact that some of the tables I had indexed views for did not have a unique/clustered index. The tables had unique indexes and clustered indexes but not a unique/clustered index. What I was seeing were rows that should have been in the view, not showing up in a regular select but they would showup in a with noexpand hint.

To fix the problem I created a unique/clustered index on each of the underlying table but cannot find that requirement anywhere, is this a requirement and if so can someone tell me where to find it.

Thanks

View 2 Replies View Related

Defrag Indexed Views?

Jul 7, 2004

How do we defrag indexed views? Can any one give me a query to loop thru all the indexed views in the database and find out the fragmentation levels and also defrag them?
Thanks in advance!

View 1 Replies View Related

Indexed Views Error

Oct 17, 2001

I have an indexed view with a clustered index on my database........when I try to run and update statement agaisnt the table that is referenced in the view, I get one of the following errors:

Server: Msg 3624, Level 20, State 1, Line 1

Location: q:SPHINXNTDBMSstorengdrsinclude
ecord.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 52
Process ID: 414

Connection Broken


Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 52
Process ID: 414

Connection Broken


any ideas?

View 2 Replies View Related

Indexed Views In Sql 2000

Mar 16, 2006

Lalitha writes "Can I use DML statements against indexed views?
If yes how it works internally, means will the pages gets locked during update and inserts and when the base tables get reflected of these modified data?"

View 1 Replies View Related

Indexed Views Without Subquery

Mar 19, 2008

Hi, I'm having a problem creating an indexed view of a productprice table

CREATE TABLE [dbo].[ProductPrice](
[ProductPriceGUID] [uniqueidentifier] ROWGUIDCOL,
[ProductGUID] [uniqueidentifier],
[Price] [decimal](7, 2),
[IsSRP] [bit],
[EffectiveDate] [datetime]
)

Each ProductGUID can and should have multiple records in the ProductPrice table. I want to create an indexed view that will group by ProductGUID that will show either the most recent custom price if a (isSRP = 'False') record exists or the most recent srp price if there is only (isSRP = 'True'), then a column stating whether the price column is taken from a (isSRP = 'True') record, and followed by the most recent srp price (isSRP = 'True').


SELECT
[PP].ProductGUID AS [ProductGUID],
CASE
WHEN MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) = 0
THEN MAX(CASE [PP].IsSRP WHEN 'True' THEN 0 ELSE [PP].Price END)
ELSE MAX([PP].Price)
END AS [Price],
MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) AS [PriceIsSRP],
MAX(CASE [PP].IsSRP WHEN 'True' THEN [PP].Price ELSE 0 END) AS [SRP]
FROM dbo.ProductPrice AS [PP]
-- Eliminate all but the most recent custom price, and msrp price
WHERE ([PP].IsSRP = 'True' AND [PP].EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'True'))
OR ([PP].IsSRP = 'False' AND EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'False'))
GROUP BY [PP].ProductGUID

This query works, but I can't create an indexed view on any query with a subquery. So, If I had some way of limiting the query to return only the most recent IsSRP = 'True' and the most recent IsSRP = 'False' (if one exists) without using a subquery then this would be aces.

Oh, and every ProductGUID has at least one IsSRP = 'True' record.

Any ideas??

View 20 Replies View Related

Use Of Union In Indexed Views

Feb 5, 2007

hi frends.i have such a great problem.i want to use union in indexedview and i must have to use it. because i cant make a one tablebecause in those two tables there are 2,000,000 records entereddaily.So please give me suggestion. can also give me alternate ofusing UNION in indexed view.and i want to use idexed view only becausei will do searching afterwards.Regards,Mr.MirzaSoftware Engineer.

View 1 Replies View Related

Indexed Views And Context

Apr 30, 2007

Hi guys ,
we had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?


If yes how can I implement .Because data depends on context is there any way to create global indexed views.


In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

View 2 Replies View Related

Looking For A Good Article About Indexed Views.

Apr 29, 2004

Hello folks!
I'm looking for a good article about Indexed Views.

Cheers!
Rafael

View 1 Replies View Related

SOS - Table Partitions Or Indexed Views.

Feb 26, 2008

Hi Experts,

We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years).

Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database.

Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.

1. Will SQL server table paritioning help in this scenario ?

Or

2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?

Any help would be greatly appreciated.

Thanks in advance,

Hariarul

View 4 Replies View Related

Updating Indexed Views - Who Pays?

Jul 23, 2005

This from a SQL Server manual:"Complex queries, however, such as those in decision support systems,can reference large numbers of rows in base tables and aggregate largeamounts of information into relatively concise aggregates (such as sumsor averages). SQL Server 2000 supports creating a clustered index on aview that implements such a complexquery. When the CREATE INDEX statement is executed, the result set ofthe view SELECT is stored permanently in the database. Future SQLstatements that reference the view will have substantially betterresponse times. Modifications to the base data are automaticallyreflected in the view."My question arises from the last sentence. At what point are the viewsupdated with the new data? If I am running a transaction that updatessome dependent tables, is there a performance impact while the indexedviews are updated?Jess Askin.

View 3 Replies View Related

Very Serious Bug With Materialized/Indexed Views And SQL Server

Jul 20, 2005

Do not trust values returned by materialized views under SQL Serverwithout frequently checking underlying tables!!!I already posted this message under microsoft.public.sqlserver.serverand I'm amazed nobody from Microsoft answered about this problem. Byinserting lots of data into our two main tables for about 30 minutes,we can fail our materialized view that performs a count_big on thosetwo tables.Executing (after of course having stopped inserting rows in our twotables)[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView option(expand views)[/color]DOES NOT RETURN the same value than:[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView with (noexpand)[/color]The second call - using the materialized view - returns a smallernumber (as if counts were lost during our bulk insert)As our data has to be accurate, we cannot use Materialized viewsanymore. This problem does not occur when the amount of data insertedis smaller. Rebuilding the clustered index on the view fixes theproblem; do we have to constantly be rebuilding the index to keep theview synchronize !?!!?!Is there a way to tell that our view is not synchronized? Justcomparing values returned by our view does not work for us as data isconstantly been inserted.System: SQL server 2000 SP3 Enterprise EditionVincent LIDOU

View 6 Replies View Related

Indexed Views Vs Temp Tables

Jul 20, 2005

With my understanding of indexed views and according to books I read"indexed views" are supposed to perform much better than "temp tables"(temp table having primary key and indexed view with clustered indexon the same keys).But when I tried in my system I am getting opposite results. WithIndexed Views it takes 3 times more time.Any body has any reasons for that? Or my understanding was wrong?thanksRaghu Avirneni

View 2 Replies View Related

Indexed Views - Group By Redundancy

Jul 20, 2005

I have a table that I want to have a precalulcate length on a character fieldand group and sum up. Thought I could do this by creating a view with a groupby clause that includes the sum function. Unfortunately, the compilercomplains with:A clustered index cannot be created on the view 'MyView' because the indexkey includes columns which are not in the GROUP BY clause.Wish I could verbalize the problem a little better, but the following pareddown example should serve as a demonstration:SET ANSI_WARNINGS ONSET ANSI_PADDING ONSET ANSI_NULLS ONSET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET NUMERIC_ROUNDABORT OFFGOCREATE TABLE myTable(myID INT NOT NULL,RecNum INT NOT NULL,TestString VARCHAR(80) NOT NULL)GOINSERT INTO myTable VALUES(1, 1, 'a')INSERT INTO myTable VALUES(1, 2, 'ab')INSERT INTO myTable VALUES(2, 2, 'abc')GOCREATE VIEW dbo.MyView WITH SCHEMABINDING ASSELECTmyID = myID,slen = SUM(LEN(TestString)),recn = COUNT_BIG(*)FROM dbo.myTableGROUP BY myIDGOCREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)-- A clustered index cannot be created on the view 'MyView' because-- the index key includes columns which are not in the GROUP BY clause.GODROP VIEW MyViewGODROP TABLE myTableGOThanks,Chris Rathman

View 3 Replies View Related

SQL Server Destination Adapter &#043; Indexed Views

Apr 22, 2007

Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:

1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.

2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.

The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?

Thanks a lot


Mark

View 2 Replies View Related

Best Practice For Indexed Views And Aggregates Tables

Apr 10, 2007

Hi, all experts here,

Thank you very much for your kind attention.

I am having some questions on indexed views and aggregate tables.

My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?

I am looking forward to hearing from you.

Thank you very much in advance for your help.

With best regards,

Yours sincerely,

View 6 Replies View Related

Creating Indexed Views For Full Text Search

Nov 30, 2007

Hi,

I have problem that Im sure others must have had before so I am looking for advice on the best way to solve it. I have a table of text information tbl_base which is related to another table containing tags, which can contain several rows for each row in base.

I want to create an indexed view of the data for full text search. I would like to select all the tags related to a particular row in the tbl_base together into a string and join it to the end of the tbl_base table so they can be indexed along with the tbl_base data for full text indexing. Ive tried several methods but I am never able to make an index on my view because it say I cant use COALESCE, or Cursors.

Sample data here :

tbl_base

id | Text
------------------------
1 | BLah blah
2 | Dum de dum
3 | HAr HAr


tbl_base_tags

tagID | base_id | TagText
--------------------------
1 | 1 | first
2 | 1 | second
3 | 1 | third
4 | 2 | fourth


The view I want to end up with will look like this :

vw_tables

base_id | Text | Tags
-------------------------------------
1 | Blah blah | first second third
2 | dum de dum | fourth

How can I achieve this? Do I have to index all the tables seperately?

View 1 Replies View Related

Problem Creating Indexed Views With Linked Servers

Jul 20, 2005

Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!

View 1 Replies View Related

SQL Tools :: Indexed Views In Object Explorer Without Index Node

Jul 15, 2014

In the SQL Server 2014 Management Studio's object explorer indexed views with schemabinding don't have an index node. Thus, you can create and drop Indexes for views only via T-SQL. The SQL Server Management Studio 2012 still shows the index node.

Is there some Management Studio setting in version 12.0.2000.8 which I am missing?

View 2 Replies View Related

SQL Server 2008 :: Create Indexed Views On User Defined Functions?

Jul 14, 2015

I am creating mateialized view but it is failing with error that it can't be schema bound.

The query I am working to create materialized view are having joins with different tables and function.

Is it possible to create Indexed views on user defined functions?

View 2 Replies View Related

SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding

Apr 1, 2015

when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way

View 1 Replies View Related

SQL 2012 :: Indexed View - Fixing IDX From Varchar To INT?

Aug 20, 2014

I have an indexed view and i can't get why after "fixing" one of index it became slower then before.

Goal was to switch index to INT column instead of VARCHAR, so the theory looks perfect !!!(?).

I drop index and built new one with same name. Could it be because of Statistics? should I refresh it all ?

The only thing I changed is one of 3 idx:

-- CustTypeCode VARCHAR(10) /* 1,2,3,4,5 */
-- CustTypeID INT /* 1,2,3,4,5 */

-- index Before:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeCode] ASC)

-- index After:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeID] ASC)

-- usage before:
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeCode = 2

-- usage after
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeID = 2

View 3 Replies View Related

SQL 2012 :: Indexed View On Replicated Server Is Very Slow

Sep 10, 2014

So we got ServerA.dbA and ServerB.dbB and looks like nobody thought about that MVew. IT works thru linked server now, so surely enough it sucks..

Then even more: we got piece of DW on ServerRep with replcicated pieces of ServerA and ServerB, how it works I asked: "Don't worry man, we took care of everything, go directly to ServerRep for everything..."

And I see that our MView sucks even deeper, practically 0 performance, I think what going on it's still hit that linked server between ServerA.dbA and ServerB.dbB on background.I still can use it on ServerRep WITH NOEXPAND hint, which means it was properly defined...

Can we have that scenario at all? Or we can replicate all Tables on ServerRep and then define/redefine this view directly on replicated server ?

View 4 Replies View Related

Transact SQL :: Returning Non Indexed Records From Sorted Table - Speed Same As Indexed?

Dec 1, 2015

I have created a table from another table where I specified that one of the fields, an number field,  is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.

Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?

In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?

Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?

View 15 Replies View Related

SQL 2012 :: Re-Indexed With Wrong Fill Factor And Now All Databases Are Huge

Jan 24, 2015

As the title says I re-indexed all of my databases using the wrong fill factor. Instead of using 90% as the fill factor I misunderstood and set this at 10%. So I believe my databases are now packed with a ton of unused space. The DB sizes should be about 5-6 GB but have since grown to 20-40GB. I am very new to SQL administration and don't know of a safe way to remove this unused space so that my databases return to their normal sizes. The databases do not grow very much at all so the free space is not really that necessary.

View 9 Replies View Related

SQL Server 2012 :: Query To Search Full-text Indexed Table And Return Results

Apr 19, 2014

I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)

Query

DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);

SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');

SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result

[Code] ....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved