copy index statistics from production to development.

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - Slowly Changing Dimension index usage We're using slowly changing dimensions to control a number of data tables in our system. Each table has five or six business keys, but the indexes of the tables are built so they're as efficient as possible (i.e. the fields with the highest diversity are listed first). How does the SCD wizard determine the order of the business key fields Is there a way I can view or manipulate the statement the SCD task is using to make sure either (a) the indexes match the statement, or (b) the statement matches the indexes Tag: copy index statistics from production to development.
  - 2
    - Add prior period data to matrix in MDX I have the following MDX that returns a balance per product type on the date the user selects from a parameter SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS , NON EMPTY { ([Book Balance Type].[Balance Type].[Balance Type]. ALLMEMBERS * [Products].[ProductID].[Product ID]. ALLMEMBERS * [Accounts].[Acct Rev Group ID].[Acct Rev Group ID]. ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION , MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET (@TimeSimpleDate, CONSTRAINED ) ) ON COLUMNS FROM [DailyBalances]) WHERE ( IIF ( STRTOSET (@TimeSimpleDate, CONSTRAINED ). Count = 1, STRTOSET (@TimeSimpleDate, CONSTRAINED ), [Time].[Simple Date]. currentmember ) ) CELL PROPERTIES VALUE , BACK_COLOR , FORE_COLOR , FORMATTED_VALUE , FORMAT_STRING , FONT_NAME , FONT_SIZE , FONT_FLAGS This returns something like Product | Balance How would I got about adding what the balance was 1 month prior to the selected date I can't seem to get my head around the PARALLELPERIOD function, although I figured that's what I may have to use to get this data to get something like Product | Balance | Last Month Balance Tag: copy index statistics from production to development.
  - 3
    - Project 2007 Server Cube Build Fails Connecting To Another SQL 2005 Analysis Server thanks, yeah I followed that page completly. What other possible users aside from the SPPAdmin user could it possibly be trying to use. The project server and the sql server are on the same domain. Tag: copy index statistics from production to development.
  - 4
    - Upload a Sql website to a host server Yes I did But it still does not work Tag: copy index statistics from production to development.
  - 5
    - SQL 2005 Transact-SQL Full Outer Self Join as Outer joins will return NULLs for columns that belong to the non-matching rows and the WHERE clause you have will eliminate those rows. You can do the following: SELECT T1.product, T1.vendor, T2.product, T2.vendor FROM (SELECT * FROM product_vendor WHERE product = 1) T1 FULL OUTER JOIN (SELECT * FROM product_vendor WHERE product = 1) T2 ON T1.vendor = T2.vendor Another way to write the query without FULL join is to do below. This will perform much faster than the FULL JOIN especially for larger data sets. select t.vendor , case t.cnt when 2 then 'Vendor sells p1 & p2' when 1 then (case when p2 is null then 'Vendor sells p1 but not p2' when p1 is null then 'Vendor sells p2 but not p1' end) end as product_status from ( select p.vendor, min(p.product) as p1, max(p.product) as p2, count(distinct p.product)_as cnt from product_vendor as p where p.product in (1, 2) group by p.vendor ) as t Tag: copy index statistics from production to development.
  - 6
    - Data entry in a datetime field. You're probalby not getting a reply because your issue doesn't make sense. There seems to be a terminology issue here... You keep saying that the date value can only be inserted via an Insert statement - well, that's the only way to add new records to a database. An Update statment will work as well if you are updating an existing record. Date controls are provided that support databinding and the datagrid also supports datetime entries. Perhaps you need to do one of the VB training walkthroughs on data access. Most likely you have a problem with your core data access structure and this datetime issue is just a side effect. Tag: copy index statistics from production to development.
  - 7
    - Upgrade Hey guys - I need your help. We are using SQL Server 2000- do you think, we should upgrade to some later release of Microsoft SQL Servier or Migrate to Oracle, IBM or Sybase. I am also wondering, what do you think of Open Source MySQL and PostgreSQL. Have anyone of you using these databases. Thanks guys for your help - Mark Smith Tag: copy index statistics from production to development.
  - 8
    - Matrix Report Not Showing All Columns Thanks, Ham. Yeah, I set up the month table in the stored procedure and then returned isnull(datafield,0) and that took care of the problem. Thanks for your help. I found a similiar question that directed the person to the SQL statement and realized that was where I needed to make my changes. Thanks again. SHP Tag: copy index statistics from production to development.
  - 9
    - Access denied starting SQL Server agent Read Selecting an Account for SQL Server Agent Service in BOL From BOL You select an account for the SQL Server Agent service by using SQL Server Configuration Manager where you can choose from the following options: This account . Lets you specify the Windows domain account in which the SQL Server Agent service runs. The domain account that you specify must be a member of the sysadmin fixed server role on the local instance of SQL Server. We recommend that the Windows user account you choose is not a member of the Windows Administrators group. Local System account . The built-in local system administrator account. It is a member of the Administrators group on the local computer, and is therefore a member of the sysadmin role within SQL Server. The Local System account is provided for backward compatibility. For improved security, use a Windows domain account with the permissions listed in the following section. Madhu Tag: copy index statistics from production to development.
  - 10
    - Can I/Should I turn off logging on some tempdb transactions? TempDb is set for a 'Simple' recovery model, and you cannot change the Recovery model in TempDb. TempDb requires space, as you noticed, for the users to do their work. that space is released when the connection is released. It is often a 'struggle' to find the 'right' size for TempDb for normal operations. Reports can take a lot of space for gathering the data. Tag: copy index statistics from production to development.
  - 11
    - Released CozyRoc SSIS+ 1.1 Beta 3 Hi There, I don't have access to Vista 64 bit, but the task is implemented to automatically switch to Zip64 format, if it is required. Please give it a try and let us know how it goes. Regards, Ivan Tag: copy index statistics from production to development.
  - 12
    - OLAP, DTS and SQL Hi Jens, Thank you very much for your help. After having a look at the links, i think the easiest way for me is to program a stored procedure that executes the dimensions and cubes, and then program a sql Server Agent job to execute the sp weekly. Is possible to execute a dimension and cube from a stored procedure How would I do it I do not have administrator rights in the database. Thanks! Tag: copy index statistics from production to development.
  - 13
    - sp_ListFiles works differently based on the O/S level. Be sure to test "sp_ListFiles" carefully if you use it. It will fail on some versions of Windows because "DIR" returns different time formats. For example, Windows NT 5.2 (3790) does not return the required time format. "usp_Clean_Backup_Files" uses "sp_ListFiles" and that is how I came across this. Tag: copy index statistics from production to development.
  - 14
    - rda dropping primary key index Thanks for the reply but when running the code I get NO error... just a missing index the index should come down automatically as long as it can fit in the db size... but it does not.. it only comes down whith a smaller set of data. The error I get when creating the index manually is in sql server management studio... and there is no rda object to get rid of can you please explain your reply a little more Regards, eric Tag: copy index statistics from production to development.
  - 15
    - report i need example of report, for some busines solution, do you know where i can to find it. Tag: copy index statistics from production to development.
  - 16
    - counting all records in a database This is pretty easy: EXEC sp_msforeachtable 'sp_spaceused " "' Tag: copy index statistics from production to development.
- Next
  - 1
    - An attempt to attach an auto-named database ....\aspnetdb.mdf failed But it's impossible to find the right solution in the forum. A lot of developers with the same problem and no real successful answer. The problem is not only in the beta products, it's still in the release version. In my case I took a web project working fine from one PC to another, copy the files change all relevant path entries and nothing goes anymore with the database files. Even not when trying to run the files on a SQL 2005 Developer Server. Trying over a weekend and still no solution (even after new installation of the SQL servers). Best regards, Eckmar Tag: copy index statistics from production to development.
  - 2
    - Database in recovery Thanks Louis! It worked fine and the database eventually did recover. Tag: copy index statistics from production to development.
  - 3
    - SQL Server Replication License Requirments I'm setting up SQL Server 2005 Replication. I have purchase a processor license for SQL Server 2005. Do I need a Windows CAL for each device that will be accessing the replication via IIS Thanks! Matt Tag: copy index statistics from production to development.
  - 4
    - how to enable a user account If you mean that is disabled (as Gigi Ciubuc suggested), then it is the default behavior. Please notice that enabling guest access to model DB is highly discouraged as it will grant guest access on model, but on any newly created DB (as GigiCiubuc also pointed out). If our guess is incorrect regarding the description of your problem; can you please explain with more detail what do you mean with an abnormal guest account Thanks a lot. I hope your question was answer as expected, but let us know if you have any further questions or feedback. Thanks, -Raul Garcia SDE/T SQL Server Engine Tag: copy index statistics from production to development.
  - 5
    - Why can't I use columns based on case down in my where clause I am writing a fairly simple sql, and I would like to write something like Code Snippet select firstname as firstname, case when firstname = 'Peter' then 'yes' else 'no' end as whatever from MyTable where whatever = 'yes' And this should then select out the rows where column number 2 is 'yes'. It doesn't work, and I have to copy the firstname = 'Peter' into the where clause. But why Tag: copy index statistics from production to development.
  - 6
    - The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. (System.Data) I do not control the connection strings of SQL Server, they are controlled by the application. If their connection strings are wrong, Microsoft has some explaining to do and I want my money back for buying the developer edition. If I right-click a database within the SQL Server 2005 Management Studio, go to Tasks, then Import Data, I will always get this error if I try and import an Excel spreadsheet or Access database. It's almost as if the OleDb providers aren't installed. However, I can't reinstall them because MDAC 2.8 is already a part of XP Pro by default. I also can't register the DLLs. I get the following error: LoadLibrary("c:\windows\system32\msjetoledb40.dll") failed - The specified module could not be found. The file is there, it just does not want to be registered. Luckily, this is just a one-time datafill. I opened the file with Excel, converted it to a Tab-delimited file, then imported it into SQL Server 2005. No problems there. I would really like to get this problem fixed, though. Any other suggestions Tag: copy index statistics from production to development.
  - 7
    - Service accounts, local admin, and sysadmin question! check whether BUILTIN\Administrators group is added to sql server or not. If its added then all the User in Local Administrator group will have SA privilege. If you have removed this group then you need add the windows user to which you are mentioning to SQL Server and then you need to give permission explicitly... otherwise though its Local Admin but it will not have SA permission on SQL Server. Refer http://www.mssqltips.com/tip.asp tip=1017 Madhu Tag: copy index statistics from production to development.
  - 8
    - Incorrect Totals Do you have the option of moving Price to a dimension This is often how price is recorded if it is describing something like a product. Barring this, I would recommend calculating Units * Price in your DSV and then simply recording it in a new measure in your MG1 measure group using an additive function. Good luck, Bryan Tag: copy index statistics from production to development.
  - 9
    - Error: Subreport could not be shown. I do not see any "sql server" or "reporting services" connections programs to which I can log a bug. Is there any other way to get assistance on this issue BizTalk Server 2006 R2 Business Desktop Deployment 2007 (Beta) for Windows Vista and the 2007 Microsoft Office system Configuration Manager 2007 (SMSv4) Open Beta Customer and Partner Advisory Group for next version of Business Scorecard Manager 2005 Enterprise GO - Partner Program exFAT Feedback Tool for Expression Graphic Designer Feedback tool for Expression Web Designer CTP Feedback Tool for Interactive Designer Public CTP Host Integration Server 2006 Public Beta Program Internet Explorer Feedback Microsoft Application Compatibility Toolkit 5.0 - Beta Microsoft Dynamics Product Feedback Microsoft iSCSI Software Initiator (Boot Version) Microsoft Managed Services Solution Public Beta Microsoft Phonetic Input Tool Microsoft Robotics Studio Beta (CTP) Microsoft SMS 2003 Desired Configuration Monitoring: Configuration Manifest Program Microsoft Windows Driver Kit (WDK) Beta Program Microsoft Wireless LAN API for Windows XP SP2 beta OneNote 2003 OneNote 2007 Beta 2 OpsMgr 2007 Public Beta (MOMv3) Phoenix RDK Publisher Beta Program SCCM2007 (SMSv4) Desired Configuration Management (DCM) Nomination Survey for Partners SMS 2003 OSD Feature Pack Update - Open Beta Speech Server 2007 Beta Program SSMA V2 System Center Essentials Beta System Center Virtual Machine Manager Beta 1 Program USMT 3.0 Beta Virtual Machine Additions for Linux Virtual Server 2005 R2 SP1 Beta 1 Windows "Monad" Shell Windows Live General Beta Tester Community Windows Live Messenger + Yahoo! Messenger with Voice Beta Windows Network Developer Platform Windows Workflow Foundation technology feedback Windows XP Embedded SP2 Feature Pack 2007 Community Tech Preview Tag: copy index statistics from production to development.
  - 10
    - Converting XML Datetime to SQL Datetime Hi Konstantin Kosinsky It is not working at my machine as well, the "-" is the culprit still. Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ It does not like the hyphen "-" Tag: copy index statistics from production to development.
  - 11
    - how to get YYYYMMDD formate I use substring(cast(x+100 as varchar(3),2,2) to change 1 to 01 for month and day and year. Then use convert(char(8),'20060101',112) to change it to datetime data type. Thank you! Tag: copy index statistics from production to development.
  - 12
    - Comparison of SQL Server Reporting Services to Access Reports, Crystal Reports, Cognos or other options Thank you, you are correct there are several comparisons already on the web of which the below text is one of them from which I extracted and pasted here. Comparison of reporting solutions: Crystal Reports vs MS SQL 2000 Reporting Services Lately, I��ve been working on generating reports for a few projects. This post is an initial impression/comparison of my experiences with Crystal Reports and MS SQL 2000 Reporting Services. First, I began using Crystal Reports , the free reporting package included with Visual Studio .Net. Before I started using it, I had heard that it was an unpleasant piece of software to use. This is sort of a general overview of the process that I went through: I created an xml schema to define the fields that I would retrieve from the db. After the schema was complete, I got into starting to generate the report template using the Crystal Reports GUI designer integrated into VS.Net. The first annoyance popped up; the registration dialog that bugs the *** out of you. The designer was fairly straightforward and I have little issues with that. You can right-click on the report and select ��view source,�� to see the generated c# code representing the report template. After the initial template was created, I created a class to generate a dataset based on user input. The class calls a stored procedure which then returns fields in the same format as those specified in my xml schema. The dataset is then created with these fields and used as the data source for the report. At this point, I built an .aspx page with a CrystalReportViewer object. After binding the dataset to the report object and the report object to the viewer object, you finally can view the report from a browser. To me, this seems like a hell of a lot of work for generating reports. Next, I got into a project that didn��t have a reporting solution. For this project, I chose to use MS SQL Reporting Services to generate the reports. SQL Reporting Services integrates nicely with Visual Studio .Net and best of all... no annoying registration dialog! One of the differences in VS.Net that initially threw me off was that I had to create a separate project to house Reporting Services reports. This is different than Crystal Reports, which just lets you add a report (.rpt) to any project in the solution. The point behind having a separate Reporting type project is mainly for deployment issues (I think) and it is a nice way to keep the reports organized. The two main parts of the report project are shared data sources and reports. As the name implies, you can setup one or many data sources to be shared between reports. Generating reports seems to have a little learning curve, but overall is a pretty easy procedure. There are wizards to drive you through the initial process, if that is your thing. Once again, the GUI editor for reports is friendly and done pretty well, but it contains some very cool features that Crystal Reports does not. First, it uses tabbed views to switch between modifying the data source, layout, and dun dun dah.... preview. The layout view is where you are able to modify the look and feel of the report. You can then view the expected result (using real data from the data store) by clicking the preview tab. Right-clicking on the report and selecting ��view source,�� presents the underlying xml used to represent the report . My initial thought is that it��ll be really nice and easy to generate my own XML representation, if the need arises. Now, rather than having to generate a separate .aspx page to view the report results, you can deploy the report to the reporting server. The reporting server is basically a server that has the reporting services web service installed. Reporting services uses this web service as their front end to reports too. So, you can just go to the web service and navigate to your report, to view it. If you need to specify parameters to limit the query, the web service will auto generate a framed webpage to do this for you. This is all well and good, but exposing the reports via a web service also allows you to build your own front end, if desired. Now there are many other cool features that I��m finding with SQL Reporting Services (drill down and access to any .Net class within the expression syntax, to name a few) and I��ll try to write about those in other posts. For both solutions, the documentation is pretty minimal. I have to give the edge to MS SQL Reporting Services, since they do have a section on MSDN devoted to reporting services . Also, Reporting Services provides a sample solution with multiple reports, showing what is possible. Even though the reports are different, the performance seemed to be pretty much the same for both reports. I��m not saying that I benchmarked the report performance, but visually I didn��t notice that one reporting package was faster than the other. Also, both reporting packages allow for reports to be exported to a variety of formats (Excel, PDF, Word, etc..) which is nice. All in all, I will choose to go with MS Reporting Services for any future reporting needs... Tag: copy index statistics from production to development.
  - 13
    - Changing the default database files path fails hi Jenz thanks for your reply. the code posted earlier worked smoothly just after a computer restart.the error message was either "Create failed for Database 'SMO_TEST'." or "Drop failed for Database 'SMO_TEST'." with whatever mistake i made, which provided no help in correcting the problem. however another issue arised when i tried to use a transaction using either method connection.BeginTransaction(); or server.ConnectionContext.BeginTransaction(); (1) how to correct this problem and (2) what kind of transaction occures here is it a distributed transaction also i would like to ask (3)if there is any way to diable full text indexing other than using connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @action = 'disable'" ); (see the code below) . sql server management studio creates the database with full text indexing disabled by default. but when using smo it is enabled by default and the documentation claims the opposite. (4) so where does smo take its default values from string sqlServerInstance = "localhost" ; string databaseName = "SMO_TEST" ; string dataFileLogicalName = databaseName + "_Data" ; string logFileLogicalName = databaseName + "_Log" ; string dataFilePhysicalName = dataFileLogicalName + ".mdf" ; string logFilePhysicalName = logFileLogicalName + ".ldf" ; string dataFilePath = "D:\\TEST_DIR\\" ; string logFilePath = "D:\\TEST_DIR\\" ; if (! Directory .Exists(dataFilePath)) { Directory .CreateDirectory(dataFilePath); //throw new DirectoryNotFoundException("The data file directory " + dataFilePath + " does not exists."); } if (! Directory .Exists(logFilePath)) { Directory .CreateDirectory(logFilePath); //throw new DirectoryNotFoundException("The log file directory " + logFilePath + " does not exists."); } string defaultFileGroupName = "PRIMARY" ; ServerConnection connection; connection = new ServerConnection (sqlServerInstance); Server server = new Server (connection); try { //connection.BeginTransaction(); server.ConnectionContext.BeginTransaction(); if (server.Databases.Contains(databaseName)) { server.Databases[databaseName].Drop(); server.Alter(); } Database database = new Database (server, databaseName); FileGroup fileGroup = new FileGroup (database, defaultFileGroupName); database.FileGroups.Add(fileGroup); DataFile dataFile = new DataFile (fileGroup, dataFileLogicalName); dataFile.FileName = dataFilePath + dataFilePhysicalName; dataFile.GrowthType = FileGrowthType .KB; dataFile.Growth = 1024d; fileGroup.Files.Add(dataFile); LogFile logFile = new LogFile (database, logFileLogicalName); logFile.FileName = logFilePath + logFilePhysicalName; logFile.GrowthType = FileGrowthType .Percent; logFile.MaxSize = 2147483648d; database.LogFiles.Add(logFile); database.Create(); // disable full text indexing connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @action = 'disable'" ); //connection.CommitTransaction(); server.ConnectionContext.CommitTransaction(); } catch ( FailedOperationException ex) { //connection.RollBackTransaction(); server.ConnectionContext.RollBackTransaction(); Console .WriteLine(ex.Message); } finally { if (connection != null ) { if (connection.IsOpen) { connection.Disconnect(); } connection = null ; } } thanks again Tag: copy index statistics from production to development.
  - 14
    - SSRS in Sharepoint, WrapperReportRenderingException/JobCanceledException for large Matrix (OLAP) reports You seem to be running into a problem by using HTML renderer in SharePoint mode. However, we are not aware of any such problem with large matrix reports in the HTML renderer. Can you send us the contents of your report rdl file thanx, Prash Tag: copy index statistics from production to development.
  - 15
    - AMO:Can any one knows hoe to rename a role and permissions to that role Thanks Darren. Tag: copy index statistics from production to development.
  - 16
    - RDA - multi table error for tracking You could make a single table view and pull this instead (but the view must reference only ONE table). A primary is required if using RDA tracking. See http://msdn2.microsoft.com/en-us/library/ms172971.aspx "A primary key must be defined on the updatable recordset returned by the SELECT statement. " Tag: copy index statistics from production to development.

TheBrick

I have a very large database that I need to be able to copy the statistics only from the production database server to my develoment environment. I need to be able to reproduce the execution plans on development without moving all the data. MS does this when they need to review the exeecution plan for large complex databases.

Need help.

Re: copy index statistics from production to development.

hunchback

See if this helps.

Transferring SQL Server Statistics From One Database to Another

http://www.sql-server-performance.com/jc_transferring_statistics.asp

AMB

Re: copy index statistics from production to development.

TheBrick

Thanks for your help.

Re: copy index statistics from production to development.

Umachandar Jayachandran - MS

The technique described in the link is not a supported mechanism. It will not work in SQL Server 2005 also. It is risky to manipulate system metadata especially page pointers from one database to another. You will end up corrupting the database. The ability to create statistics and histograms for indexes is available in SQL Server 2005. You can use the SMO scripting capability to do this and create an empty database with all the stats from production. See the following topics for more details:

http://msdn2.microsoft.com/en-us/library/ms186472.aspx -- Use Script Statistics option