Tag Info

New answers tagged

1

The 'AUTO_UPDATE_STATISTICS' Option has two possible values, False(off) and True(on). When set to False, 'AUTO_UPDATE_STATISTICS' will not update any statistics. When set to True, 'AUTO_UPDATE_STATISTICS' will update statistics. The method chosen to update the statistics though depends on the value of the 'AUTO_UPDATE_STATISTICS_ASYNC' option. This option ...


1

No ambiguity there. If you set 'Auto Update Statistics' to 'False' statistics will not be updated automatically. If you set 'Auto Update Statistics' to 'True' they will be updated according synchronously or asynchronously according to to the 'Auto Update Statistics Asynchronously' settings.


2

The data should be "the same" as on my machine, that I can dismiss my server and point to new machine in the end. Based on your comments, your best option would be Database Mirroring or Log shipping. You have to evaluate your RPO and RTO. Since you are using VPN to your actual machine - meaning it is point-to-point connection : If you go with ...


2

It sounds like you're trying to setup a disaster recovery environment, so that if your local machine blows-up you can switch over to the Azure machine. There are a number of options, each with their own pros and cons. These are well documented elsewhere (see this link for an overview from Brent Ozar: ...


2

The scripts go over all of your tables, rebuild all the indexes and update their statistics. The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable. I'm not sure, though, why you need to run the first command which disables the indexes.. ...


2

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access. The commenters ...


0

I'm not entirely sure if I'm right, but as far as I know solution 1 will not work since you can't add two foreign keys in the same column and even if it is possible the values will probably overlap, such as existing a UserId = 1 and a CompanyId = 1 causing you not to know which of them is the true one, furthermore thinking of a big solution you may even ...


13

That is just documented behavior. I don't think anyone messed with the settings. See data type precedence on MSDN. As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date. EDIT: I think your real problem is that your design is so that you have to join on fields of a ...


0

You can use a sort of UNPIVOT with CROSS APPLY VALUES, like this: DECLARE @Addresses TABLE ( [Address] nvarchar(100), [City] nvarchar(100), [ZIP] char(5), [State] nvarchar(100) ) INSERT INTO @Addresses VALUES ('#500-75 O''Connor Street','Ottawa','K4B 1','Ontario'), ('#9900 2700 Production Way','Burnaby','V5A 4','British Columbia'), ('00, ...


0

If you want to know which column matched the parameter your best bet is probably to use a union query such as the following. set @param = '%new%'; select address, city, zip, 'address' as matched_on from mls_data where address like @param union all select address, city, zip, 'city' as matched_on from mls_data where city like @param union all select address, ...


2

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague). ...


0

If there is a particular plan you want it to use, regardless of the statistics, then try using a plan guide for that query. It seems pointless to try to persuade the optimiser when there's a known behaviour you want to use. It's better to update your stats AFTER your ETL, not just to persuade your ETL to run well.


2

move to SQL Server 2014 that offers updateable clustered columnstore index. if stuck to 2012, use partition switch to update the nonclustered columnstore index.


6

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column. SELECT col1,col2,col3 FROM t1 UNION ALL SELECT col1,col2, NULL FROM t2


0

Evaluate the result from the proc (with an IF statement) and then if you want it to fail the job execute a THROW command. See this article.


0

You can first insert the results from the stored procedure into a temporary table (see http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table), and then simply query that table with your conditions. Let's say that you put the results in a temporary table called #procData. In that case, your final query would ...


1

You can go directly to the tempdb properties and change the makeup of the database like you would any other database. The difference here is that the changes you make will reset the initial creation size when SQL Server starts up. It will also affect the current tempdb database that is live. So if you add more files in properties, it will actually add the ...


2

Sure, you can do it with little risk, but it depends on the state of your current tempdb. If tempdb has grown quite large, you might need to shrink it first before you have enough space for perhaps 4 good size tempdb files. Because of how active tempdb is and how hard it is to shrink that you might need to schedule a little downtime to make the clean ...


1

There really isn't a concept of a connection string in the Availability Group. The AG is based on a Windows Cluster and a Virtual Name in the cluster that is of the type of Availability Group Listener. This listener is configured in Management Studio in the Availability Groups container in Object Explorer. The example that was given above is correct. SQL01 ...


2

(since this is longer than a comment ... so putting it as answer) For one of our DB's on SQL server 2012 RTM. This is your problem. There were many fixes introduced in the consecutive CUs / SPs after the RTM build. Can you patch the sql server with the latest SP2 ? After patching, check if the issue still happens or not. Your problem might be because ...


0

Profiler does not capture point in time information (ie, what is the status of this query or wait times/types or current blocks, however you can get deadlock events). But rather, it captures activity/event information (ie, this activity/event occurred so record it). As such, it will not be able to give you that kind of information since it is always runnable ...


0

Increase the width of date column in design part on the SSRS. This is the compress problem on excel sheet. so give the full space for date and time which will appear on full length.


0

Just making sure - Is the log file set to a fixed size? If so, try adding a secondary log file to the database and then do a checkpoint. If all else fails rebuild the log. ALTER DATABASE <database> set EMERGENCY ALTER DATABASE <database> REBUILD LOG ON (NAME='<database>',FILENAME='<filename>') ALTER DATABASE <database> set ...


1

Schema generation will totally depend on the amount of objects (tables, SPs, triggers, indexes, etc) in database. Schema is just a skeleton of the database without data. If you are concerned about using SSMS GUI, you can use PowerShell - which will definitely scale better - and can be automated easily as compared to SSMS GUI. I have tried scripting out ...


9

The only alternative to performing consistency checking (DBCC CHECKDB) is to leave your data open to the possibility of corruption and data loss. There are some ways to reduce the impact that CHECKDB has on your system. The best way to do this by far is to run your checks offline on a copy of your live systems. This should be done by restoring a full ...


0

I wanted to use Powershell to script a small database so that I can track changes in git. I found the same script you did to use as a starting point: Automated Script Generation with Powershell and SMO You can find all of the options for the ScriptingOptions class here: ScriptingOptions Class As @Kin points out, you're getting the error because of the now ...


8

UPDATE t2 SET flag = 1, flagdate = CURRENT_TIMESTAMP FROM dbo.table2 AS t2 INNER JOIN dbo.table1 AS t1 ON t2.id = t1.id WHERE t1.amount > 10000 AND (t2.flag IS NULL OR t2.flag = 0); You can avoid the OR condition if you change the flag column to NOT NULL with default 0.


2

Have you tried this? SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ] FROM ( SELECT Id, StatusDate, 'Results_201505' as tbl FROM Results_201505 WHERE A = 0, B = 1, C = 3 UNION ALL SELECT Id, StatusDate, 'Results_201504' as tbl FROM Results_201504 WHERE A ...


0

One more difference between snapshot and read committed snapshot is the following. Snapshot In the first session SET TRAN ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT * FROM TB1 ..... ..... In the second session Update TB1 SET NAME = NAME + 'test' Where id = 1 In the first session SELECT * FROM TB1 -- THIS ...


3

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there ...


3

I can reproduce the bad plan. I found three workarounds: OPTION (RECOMPILE) INNER LOOP JOIN hints A nasty, crazy rewrite: . SELECT y.* FROM (VALUES (@CustomerPartitionKey)) x(CustomerPartitionKey) CROSS APPLY ( SELECT --TOP 300 CI.ContactId, I.Ordinal, I.Identifier FROM #identifiers I INNER JOIN ...


0

I ended up using F5 BIG-IP LTM Virtual Edition, because I didn't find any other free solutions, that fits my business requirements.


1

What is the exact type of the user_id column? It is very likely not a VARCHAR column therefore the rules of data type precedence dictate that the comparison must be done by casting the user_id to the type of the '10' literal, thus negating the possibility of an index seek and forcing a full scan. This is a very common mistake. I recommend reading How Data ...


2

#5: transactional replication. Why is this an option? Because the documentation explains exactly why it is possible there - once initialized, index changes are not replicated in either direction. Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE ...


1

I figured out the issue after doing some more searching and trial & error. The process I used for finding the issue: Started with the CLI and ran the following command: This will provide a list of all features that are enabled and disabled Dism /online /get-features /format:table Next you use the following command to enable needed features Dism ...


3

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number ...


2

I don't think you'll be able to get good performance while using OFFSET. The database must search through 1,000,025 rows of output from the inner query; even if you have a good clustered index on TaskResults the system doesn't know for certain that it can skip ahead to date X. But you do! Assuming this is for some kind of GUI, make a note of the earliest ...


1

I'm not entirely clear on whether I understand you problem or not, but assuming there is a customer_id attribute you can partition by that: select ... from ( select ..., row_number() over (partition by customer_id) as rn from ... ) where rn = 1 This will give you one row per customer, in an undefined order. If you want a true random account per ...


0

Change $SqlServerPrimName = "SERVER\INSTANCE,Port" to $SqlServerPrimName = "SERVER\INSTANCE" and change the port for the instance afterwards. $ComputerName = gc env:computername $SqlServerPrimName = "SERVER\INSTANCE" $SQLServerPort = 1433 $wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $computerName ...


1

1) Make sure the account running the SQLservice should have the required rights on the directory you are wishing to change: 2) Also, you can give a try with below T-SQL You can also do the same with T-SQL and here is the T-SQL code to do the same. /* I am trying to change to D drive in below example*/ USE [master] GO EXEC xp_instance_regwrite ...


1

Shrinking a database is not a good idea. There are specific cases, but they require a DBA to carefully evaluate the situation. Sql Server cannot have an idea if a query in, say, next five minutes is going to hit tempdb heavily. Without such a knowledge, shrinking and expanding tempdb is going to be unnecessary and, to be honest, stupid too. TempDB is ...


2

If you have index with just the status field, most likely it will not be used because for each row found from the index the rest of the columns you select must be fetched from clustered index and these key lookups will be expensive and most likely it is more efficient just to scan the clustered index. If you have other fields in the where clause, a ...


0

I've come to the conclusion that what i wan't is not possible in MDX. But it don't need to be. I also realized MDX is not SQL. SQL returns a dataset. MDX returns a dataset and metadata. Combine the two and I get what I wanted. Pulling up my original query into an Excel PivotTable it was exactly what I was after. So it came down to SSMS limitations at ...


2

Adding index on low cardinality column can make sense for instance in case you need to search for value which is quite rare - say you have column State with possibities of New/Processed and 95% rows set as Processed, and you have to get those New often - then index will help you find them fast. But having it present means optimizer can try to use in bad ...


0

To start with you have SQL Server 2012 and memory architecture changed significantly in this version there is only one memory allocator (yes there is VAS allocator as well but I still would say there is only one 'Any Page' allocator) and this does Almost All memory allocations. Here is what BOL has to say. Please read this definition clearly Max server ...


0

Regardless of your recovery model whenever an operation (index rebuild or reorganize) modifies a data page that page modification is put into the log. In your case changing the fill factor can add more data pages, and move data to new pages. SQL Server has to make room for the new data and to do that it has to keep track of the page changes. The more ...


2

The only option close to what you need is to mark the control as "Begin a Group", to create a separator to the left of the control. Open SSMS > Tools > Customize > Commands tab > under Toolbar select "Standard" > highlight "Activity Monitor" > click Modify Selection and you can (un)tick "Begin a group" and see how the toolbar changes. You could edit the ...


1

If you've set-up an Always On Availability Group Listener (and you should), then you'll have a new virtual name to use in your connection string. So if your SQL Servers are SQL01 and SQL02, and there's an availability group called SQLAG01 - then you'll specify this. You could continue to specify SQL01 in your connection string, but if the Always On ...


1

Answering my own question, as future readers will benefit from it : Seems like we might be hitting Longer latency for SQL Server 2012 database when you use Service Broker, database mirroring, and Availability Groups. This is fixed in SQL server 2012 SP2 CU1. The KB 2976982 has a typo(AlawysOn). So if you are searching by AlwaysON, it wont show up. After ...


0

I've been experiencing the same thing. Worse yet my Snapshot gets selected as a deadlock victim and although the Snapshot and the Publisher think the Snapshot completed successfully, the Distributor never picks up the Snapshot files and does not play them into the subscriber. As a result when transactions affecting the new articles arrive the distribution ...



Top 50 recent answers are included