When to reindex?

SQL Server Database Engine

Index ‹ SQL Server

- Previous
  - 1
    - Cubes and Cost-Forecast Hi, I'm experiencing trouble with the following requirement: We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table: projekt, date_of_forecast, date, costs PR-A, 2007-06-01, 2007-07-01, 2000 PR-A, 2007-06-05, 2007-07-01, 3000 PR-A, 2007-06-10, 2007-07-01, 2500 PR-A, 2007-06-10, 2007-07-15, 2000 For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15. The customer wants a cube wich allows an answer to the following question: Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ... How to accomplish the using SQL Server 2005 STANDARD-EDITION Do I need two Time-Dimensions or just one Is there a way to create a Cube-Skript for this requirement Thanks and best whishes Manfred Tag: When to reindex?
  - 2
    - INSERTING NULL VALUES VIA STORED PROCEDURES Hi, Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this: errorParams[3].Value = (e.InnerException== null ) ( object ) DBNull .Value:( object )e.InnerException; When I do it like this ( it is a part of preparing values for insert ): errorParams[3].Value = e.InnerException no row is added. Is there any way to pass over that check : (e.InnerException== null ) ( and do it easier This the sample exception message for that issue: {System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@InnerException' , which was not supplied. Thanks, Pawe Tag: When to reindex?
  - 3
    - Parse Error I am getting the following error whenever we tries to parse any SQL statements in Micorsoft SQL Management Studio 2005 ЁА.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.ЁБ Please help me. Svas Tag: When to reindex?
  - 4
    - Full text prefix search for phrase with noise word? I have data (in SQL Server 2005 SP2) that looks like: ID, Name 1, elbonia university of medical science 2, elb univ med sci 3, elbonia university, med sci 4, elbonia has universities, blah blah blah medium sized scientists And a query that looks like: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ med sci*"') I want it to get 1-3 and not 4, but it just gets 2 and 3. I think the noise word 'of' is throwing if off but if I try: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ of med sci*"') I get nothing! WTF Does a noise word break prefix searches The only message is " (0 row(s) affected)" SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science"') returns 1 but add the * for stemming: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science*"') and it also returns nothing. Thanks for looking! -Andrew Tag: When to reindex?
  - 5
    - SQL Server Express error when using NUnit This question is for someone experienced with both SQL Server Express and NUnit. I think the answer might be simple, but I would like to understand what is going on and how to prevent it. I have a test class with a TestFixtureSetUp section that calls a method to drop and re-build my database from scratch, including tables for the Membership and Roles provider. This code works fine. After TestFixtureSetUp runs, I have a test to verify that there are 3 records in the Membership database which were added when the database was re-built. The code looks like this: [ Test ] public void MembershipUsersTest() { int expectedNumberOfUsers = 3; MembershipUserCollection users = System.Web.Security. Membership .GetAllUsers(); Assert .AreEqual(expectedNumberOfUsers, users.Count); } When this test runs along with the TextFixtureSetUp code, I get this error: System.Data.SqlClient.SqlException : Cannot open user default database. Login failed. Login failed for user 'TERRY-COMPUTER\Terry'. However, if I re-run this test but block the TestFixtureSetUp code that re-builds the database, the test passes fine. I'm guessing it's just a simple matter of NUnit somehow locking access to the database when the database is rebuilt, but I am not understanding why this is happening. My code for re-generating the database uses a "using" construct, so the connnections should be closing properly. using ( SqlConnection conn = new SqlConnection (connectionString)) { FileInfo file = new FileInfo (fullScriptPath); string script = file.OpenText().ReadToEnd(); Server server = new Server ( new ServerConnection (conn)); server.ConnectionContext.ExecuteNonQuery(script); } Any insights into why I am getting this error and how I can avoid it and still run my Membership test right after I re-build the db One additional piece of information that might be important. The code for rebuilding the databases uses the following connection string: @"Data Source=.\SQLExpress;Integrated Security=True" Whereas the Membership provider uses the following connection string which has a hard-coded path to the database: " Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=C:\MyApps\MyAppsWeb\App_Data\MyApp.mdf " Tag: When to reindex?
  - 6
    - Best practice to copy a lot of data between databases Hi, I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables. Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows: Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database) Some people do some checks to see if the new data in the Test-database is correct If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database. For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up Is my current way of working good practice Thx! Tag: When to reindex?
  - 7
    - DTS old style and Dynamic Properties Tasks Im using embedded SS2000 DTS packages in SSIS. These legacy packages use Dynamic Properties Tasks to pick up connection settings from a named ini file. The name of that ini file needs to change occasionally. To date the only way we have of doing this is resetting all the dynamic properties to point at the new ini file. Unfortuntely there are a lot of connections and properties and there is no budget to recreate these packages in SSIS. There must be an easier way ! Does anyone know how to quickly change Dynamic Properties Tasks to look at a new ini Tag: When to reindex?
  - 8
    - Help plz - need query to get the following kind of output i want result to be in the required format given below: here is the result column A ---------------- 5 7 10 14 22 30 .... i want output to be in this form with a single sql statement: column A column B ---------------- ---------------- 5 2(difference b/n the current record and the next record) 7 3 10 4 14 8 22 8 30 0 can any one plz post the query which gives the above kind of result.. thnx in advance.. Tag: When to reindex?
  - 9
    - RAISERROR does not cause SQL task to fail - why? Greetings, I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS. I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended. I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible Is there an alternative to using RAISERROR Thanks, BCB Tag: When to reindex?
  - 10
    - Moving Average using Select Statement or Cursor based? ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement Tag: When to reindex?
  - 11
    - Grouping Hi, I have a stored procedure which is returning results in the following format: No.of substances Count 1 29 2 89 3 876 .. .. 15 56 16 89 Now i need to display like this: No.of substances Count % 1 20 2 89 .. .. >=9 8766 Total All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9 How to achieve this Thanks in advance Tag: When to reindex?
  - 12
    - Problem with "phantom rows" Hi, I am seeing something strange. I have a data file that has 6 rows that looks like: ABC, "OPENING BALANCE", 1234, etc ABC, "CLOSING BALANCE", 1235, etc ABC, garbage data, etc XYZ, "OPENING BALANCE", 1234, etc XYZ, "CLOSING BALANCE", 1235, etc [][] -- weird box things that shows up in my flat file conn mgr I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines. I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should. However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them. I really don't understand how this is happening. Please, any ideas. Thanks Tag: When to reindex?
  - 13
    - SQL Express Help Files Where do I find the Help files for SQL Express. When I click on help it says, help is not available. Tag: When to reindex?
  - 14
    - Design question - Foreign key indexes A rather elementary design question - is it a good idea to index foreign keys in fact tables I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time Does the cube generate all dimension members then query fact tables for them Does it query fact tables with group by's on participating dimensions I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed. Thanks in advance, John Hennesey Tag: When to reindex?
  - 15
    - ADO Error: an insufficient number of arguments.... Im currently using Access 2000 which is connected to a SQL database. I have a query (Or View) that runs every month. The problem i had was i had to manually make some changes every month to get to the data i needed so i changed this View into a Function with a parameter so i can input the detail(s) i need and it will run correctly - this works so far but the original View was also used within other queries that were created. Now the problem i have now is some of the other views that are now connected with the newly created Function comes up with the error: ADO error: an insufficient number of arguments were supplied for the procedure or function Name_Of_Function. I called the newly created function the exact name as the original view to ensure i had no problems. Any idea of whats happening here and how to resolve Thanks Tag: When to reindex?
  - 16
    - Replication looking for errant sysserver links We have replication successfully running but when I tried to set up a new test instance on completely different servers (Server A to Server B, say) the push agent to create the susbcription on Server B complains 'Could not find server 'Server C' in sysservers. Execute sp_addlinkedserver. . . .' Server C was never configured as a subscriber for the publication in the first place. What's more, Server C is actually a working linked server and definitely is in sysservers for Server A. I originally tried this on yet another server pair and got a similar message referencing a server name that no longer exists. I stopped messing with that one, figuring it was some historical naming issue I didn't want to mess with. But now I'm stumped and intrigued. Can anyone help Tag: When to reindex?
- Next
  - 1
    - Calling CLR stored procedure from within a CLR table-valued function giving errors We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table. Code Snippet [ SqlFunction ( FillRowMethodName = "FillRow" , TableDefinition = "CustomerID nvarchar(MAX)" , SystemDataAccess = SystemDataAccessKind .Read, DataAccess = DataAccessKind .Read, IsDeterministic= false )] public static IEnumerable GetWishlist () { using ( SqlConnection conn = new SqlConnection ( "Context Connection=true" )) { List < string > myList = new List < string > (); conn.Open (); SqlCommand command = conn.CreateCommand (); command.CommandText = "GetObject" ; command.Parameters.AddWithValue ( "@map" , "Item" ); command.CommandType = System.Data. CommandType .StoredProcedure; using ( SqlDataReader reader = command.ExecuteReader ( System.Data. CommandBehavior .SingleRow )) { if (reader.Read ()) { myList.Add ( reader[0] as string ); } } return ( IEnumerable )myList; } } When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine. Code Snippet EXEC GetObject 'Item' Is there some sorf of trick I am missing Thank you! Tag: When to reindex?
  - 2
    - Scheduling an automatic backup Hi , I am using Microsoft SQL Server Management Studio Express 9.00.3042.00. I want to take the database backup daily at specific timing for instance, 8:00am daily. How can I take it automatically It should be take the backup on daily basis without my interference. with programatically Can anyone help me i neeed answer with clear example or screen shots. Thanks in advance Tag: When to reindex?
  - 3
    - Emailing Report I need to export a view into excel and mail to my manager daily morning. Is there a way i can automate this process. I know how to convert view to excel but i need a better solution to automate this process with emailing to my mgr. Please suggest a better approach of doing this. Thanks, Tag: When to reindex?
  - 4
    - Documents in VLDB Hello All, I have been experimenting with SQL Server 2005 partitions. I loaded a terabyte of information into 2 tables. The first holds the document information and the second holds the actual binary document (in this case pdf). Most of the documents are about 1 megabyte in size, but the largest is 212 megabytes. SQL Server has no problem storing the blobs. The problem occurs when I attempt to get the data. I did some quick tests to test how fast I could pull the documents out. The largest took about 24 seconds. The 1 meg documents are sub-second. Here is how the 212 meg doc breaks down: Time to load datatable: 18.79 seconds Time to load byte array: 3.84 seconds Time to Write and open document: 0.01 seconds If I access the file from a file server, the time is 0.04 seconds to begin showing the document. As you can see, the longest time period is related to retrieving the data from SQL, and it is much slower that launching it from disk across the network. (note: the sql server and file server used to test are next to each other). My question is, how can I speed up the access from SQL Server I believe the keys are "partition aligned". Any suggestions would be appreciated. I will add the table definitions and partition information as a reply since only 5000 chars are allowed in the post. Tag: When to reindex?
  - 5
    - Scheduled Backup suddenly started giving errors Hi, I have scheduled full backup daily at morning 6:00. It was running fine till yesterday. Suddenly today morning it failed giving following error.... I have executed maintenance plan manually and again it gave same error.... 10/19/2007 11:03:10,FullDBBackup.Subplan_1,Error,1,*****,FullDBBackup.Subplan_1,Subplan_1,,Executed as user: ****\SYSTEM. ...9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:03:10 AM Error: 2007-10-19 11:03:10.98 Code: 0xC0010018 Source: Back Up Database (Full) Description: Error loading a task. The contact information for the task is "Back Up Database Task; Microsoft Corporation; Microsoft SQL Server v9; 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. End Error Error: 2007-10-19 11:03:11.25 Code: 0xC00291EC Source: {C7FB73D1-ADF1-42C0-B3D5-9F8014CC206B} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2007-10-19 11:03:11.25 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DT... The package execution fa... The step failed.,00:00:01,0,0,,,,0 Pls help me as daily backups are very crucial to me... Tag: When to reindex?
  - 6
    - OLEDB Access PB Hi I definite a source OLEDB MS Access and my fields of the type ЁАTextЁБ are seen in type of field DT_WSTR (Unicode) instead of DT_STR. I do not include/understand why and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution thank for your solution Tag: When to reindex?
  - 7
    - Maximum number of users (licensing) I need a small SQL server for up to 10 users. If I understand the EULAs and FAQs correctly, there is no limit on the number of users imposed by the SQL Server 2005 Express license. If so, then the number of users is only limited by the OS's license so all I need is SQL Server 2005 Express (free), Windows XP Pro (10 users limit) and no CALs for either Windows or SQL Server. I'd be thankful if someone could verify this. Tag: When to reindex?
  - 8
    - Scheduling sqlcmd script in sql agent Is it possible to schedule a sqlcmd script within sqlagent That is without running a cmdexec Thanks, Tag: When to reindex?
  - 9
    - measure group not showing any data inspite the facttable having data Hi, I could successfuly deploy and process the cube. But when I go to the 'Browse' tab of the cube and drag and drop a few dimensions and measures I don't see any data. I even used simple MDX queries and even that returns 'NULL' facts. I have set the 'unknown' member as visible in the time dimension because some of the dates in the fact tables are null. Any thoughts on this would be greatly appreciated. Thanks. Tag: When to reindex?
  - 10
    - How do i change collation? I created a new server registration under my SQL Server Group. After creating the registration via EM, I looked under properties and the collation is SQL_Latin1_General_CP437_CI_AS. It needs to be Latin1_General_BIN because this is the collation order in the production server. Please bear in mind that I am not a DBA and this is all new to me, but unfortunately I'm the one who has to fix this. Please keep the terms simple in any response. Thanks. Tag: When to reindex?
  - 11
    - connecting to sql server from mobile device How can I connect to sql server from a mobile device the server is on pc and my app is on the device. I want to perform commands and stored procedures which will be performed online on the pc and get immediate results. is there any way of achiving that Tag: When to reindex?
  - 12
    - Scheduling report delivery for the first WORKING day in the month? Has anyone figured out a good way of doing this The only suggestions I have seen along these lines involve scheduling the job every day and then manually editing the SQL Agent job that is created. However the problem with this is that it completely removes the ability for non programmers to set the schedule themselves using the web interface. Any ideas Tag: When to reindex?
  - 13
    - Pivot Table Bug with AdventureWorks A user pointed out an odd bug / inconsistency that occurs when using Excel pivot tables. Depending on how attributes are related to each other, Excel does some odd things when cross joining attributes from the same dimension on an axis. Example 1. "Correct" cross join of attributes: Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount". On rows add "State-Province" and "Total Children". Row 4 should read "State-Province, Total Children, Total". Click the drop-down on "Total Children" and select "1". The results should be filtered so that you only see states where there are internet sales for families with 1 child. Example 2. "Incorrect" cross join of attributes: Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount". On rows add "State-Province" and "Country". Row 4 should read "State-Province, Country, Total". Click the drop-down on "Country" and select "United States". The results *should* be filtered so that you only see states belonging to the United States. Instead, we still see states belonging to other countries. Shouldn't the "United States" selection on the rows preclude the states from other countries I don't understand why the pairs of attributes in examples 1 and 2 interact so differently. I would appreciate any meaningful explanation than I can pass on to the user. Tag: When to reindex?
  - 14
    - Pivot Select Hi, I'm able to get Pivot to work but I'm having trouble limiting the record set. I want it to select only records from this FiscalYear. I have a table with a field called CurrentBudgetYear that I use as a control. So FiscalYear should equal CurrentBudgetYear but my results include all FiscalYears. SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6, [7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12 FROM (SELECT e.ProjNo, e.TaskCode, e.FiscalPeriod, e.ActualAmt FROM tblActualExpend AS e INNER JOIN tblBudgetConfig ON e.FiscalYear = tblBudgetConfig.CurrentBudgetYear )p PIVOT ( SUM(ActualAmt) FOR FiscalPeriod IN ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12]) )AS pvt ORDER BY ProjNo, TaskCode; Thanks in advanced for any help. Tag: When to reindex?
  - 15
    - Difference of compact edition and mobile edition Hello I recently downloaded the sql mobile edition, and then I found out about the compact edition. I understand that these are relational lite version of sql server that can run on mobile devices. However, what is the difference between the CF and mobile edition Why should I choice one over the other I have just heard about sql everywhere, seems there are all these edtions and how would you choice the right tool for the right job Many thanks if someone could explain further, Steve Tag: When to reindex?
  - 16
    - how sql server handles nvarchar Hi i'd like to know about varchar , when sql store data as nvarchar it store the actual length besides 2 bytes but the question is if there is another field does sql server store the next field besids nvarchar so there is no seprated bytes and is this exist and i update data of nvarchar does sql sqerver shifted the reamin data exist in the same track please send me the answering Tag: When to reindex?

Bobo1234

We have a fairly large database (320 tables, 70GB+) on SQL 2000 where reindexing using 'dbcc dbreindex' currently takes almost 3 hours. We are reindexing all tables and dbreindex takes up 80-90% of the system cpu making any other job slow. All indexes are setup with a fill factor of 70%.

As the database grows we would like to minimize the time spend on reindexing. One approach could be to use the output from 'dbcc showcontig' for deciding on on whether to reindex a table or not. E.g. use 'ExtentFragmentation > 10%' as the criteria for starting reindexing on a particular table. Is this a suitable approach or are there any other better parameters to consider

Rgds

Bertrand

Top

Re: When to reindex?

Arnie Rowland

I agree, re-indexing should be done only when required, not on a scheduled basis whether needed or not. A big waste of resources.

This article could prove very helpful to you:

Automating Reindexing In SQL Server 2000
http://www.sql-server-performance.com/tp_automatic_reindexing.asp

Top

Re: When to reindex?

pdxJaxon

All you ever needed to know about indexes and more....

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Top