Tag Info

New answers tagged

8

A full analysis would require access to the execution plans, table and index definitions, and database statistics (or a copy of the database itself). That's possibly unrealistic, so here's some general observations, and a possible solution for you to try. (Strictly, this question is probably beyond this site's remit.) General background The SQL Server ...


4

The question is mainly about how to optimize the select statement: SELECT [TABLE], [FIELD], [AFTER], [DATE] FROM mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND [FIELD] = 'STATUS' Removing the redundant projections and adding the presumed dbo schema: SELECT [AFTER], [DATE] FROM dbo.mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND FIELD = 'STATUS'; Without ...


6

I think that mirroring is not the the best solution in this scenario, and discontinuation announcement is not even the main reason, especially if you're planning to maintain the test environment only for limited amount of time. Mirroring is a high-availability solution that was designed to provide hot/warm standby db in case of failure, or offload ...


2

SQL Server by default adheres to ACID database rules. The A in ACID stands for atomic, meaning that the whole transaction happens, or none of it does. The I in ACID stands for isolation, meaning that transactions are isolated from one another, so either all the results from one transaction are visible to another transaction, or none of them are. This is ...


2

If you use snapshot isolation levels, no S lock is placed on the rows, so UPDATEs are not blocked. Make sure you understand the difference between snapshot and read committed snapshot and that you test your application for these isolation levels. Also, test your workload for additional load in tempdb for the version store. Edit: RE: downside of turning on ...


1

SQL Server will send the client application some rows and wait for a response - in your case a request for another batch of rows. Generally (but not always), the ASYNC_NETWORK_IO wait is due to application design. Rather than caching the data and them sending a response to SQL Server almost immediately, it performs some type of RBAR (Row By Agonizing Row) ...


0

Login as the mystery user. Then run: select * from sys.login_token Somewhere in the output list will be token that grants you access. For instance you can try this (not guaranteed to work): select * from sys.login_token lt join sys.server_principals sp on sp.sid = lt.sid;


2

Use a Sequence. A sequence is a database-wide generator of unique integers that can provide unique surrogate keys.


0

I thought I'd create a table for quick reference. CREATE TABLE dbo.TraceDefinition ( TraceFlag INT NOT NULL CONSTRAINT PK_TradeDefinition PRIMARY KEY CLUSTERED, [Description] VARCHAR(MAX) NOT NULL ) INSERT INTO dbo.TraceDefinition (TraceFlag, [Description]) SELECT TraceFlag, [Description] FROM (VALUES (260, 'Prints versioning information about extended ...


0

Just to add, in order to emphasise a really important data management point - any aesthetic qualities of your data should be handled at the presentation/reporting/glass level of your IT application - don't let SQL do hard work that it simply doesn't need to do! Another example I see often is including the "£" or "$" symbol in every row of data to show ...


-2

This is probably extremely terrible advice but I'll post it anyways: I needed to create a SharePoint 2003 (WSS 2.0) site and I am using SQL Server 2014 for my DB Server. I could not create a site collection. I copied the contents of the sys.sp_dboption stored procedure and created it in my master database on SQL Server 2014 (in the dbo namespace) and ...


4

Yes there may be impact. To start with, you must read List of SQL Server features deprecated in SQL Server 2012 List of deprecated Database engine features in SQL Server 2012 Breaking changes to DB engine feature in SQL server 2012 You must run SQL server 2012 upgrade advisor and generate report before doing migration. This report would list out all ...


0

Can you please confirm the Compatibility level of the database you upgraded? (Right-click the database -> Properties -> Options -> Compatibility level). If this is set to 2005, please don't change it without thoroughly testing your applications, etc. as it could definitely break your environment! Just curious as to the value here. Also, I did some work with ...


0

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX You must note that latch only protects physical integrity of page while it is in memory so latch would be taken ...


2

Having a wildcard in front of your search term will prevent SQL Server from being able to use an index and thus become painful very fast. 2 things you could do, besides going for full text search: The obvisous one, do not put a wildcard at the start of the keyword requiring the user to enter a search query matching the start of col1. Splitting col1 from ...


0

Without the schema this is a little difficult to test but I think this will work. Even if I do make some syntax errors you will be able to see what I am getting at. SELECT PayrollpersonID, PayrollDeductionYear, SUM(CASE WHEN PayrollDeductionMonth = 'January' THEN Variance ELSE 0 END) AS January, MAX(CASE WHEN (PayrollDeductionMonth = ...


1

The option is still there (although this is from SQL Server 2014): You don't normally have to restart SSMS, just open a new query window.


0

If you take a look at this Connect Item, there is a comment (on a slightly unrelated problem) suggesting that you need to restart SQL Server Management Studio before any changes to the Keyboard Shortcuts take place Posted by jaredaz on 28/08/2012 at 14:13 This problem seems to go away after restarting management studio (where in prior versions it ...


0

The answer is contained in http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx 1.To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a folder where the project is in: a.Map it to a member of the SSISDB database role -- public. (This ...


2

Declare variable to get current instance name and pass value to OpenROWSet. Enable Ad Hoc Distributed Queries EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO OPENROWSET using windows authentication to fetch data DECLARE @InstanceName VARCHAR(200), @sql NVARCHAR(MAX) SELECT ...


3

You can do this silently using an INSTEAD OF trigger: CREATE TRIGGER dbo.tr_table_name ON dbo.table_name INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; INSERT dbo.table_name(col1, col2, col_you_care_about) SELECT col1, col2, UPPER(col_you_care_about) FROM inserted; END GO If you want to raise an error, you can use a CHECK constraint: CREATE ...


2

It seems that when setting the directive (the 4th item in the !-delimited specification that is the field name alias) to "element", then the 3rd item in that specification is the element name that contains the value, whereas the 3rd item would otherwise be the attribute name that contains the value. In both cases the 1st item in the specification is the ...


3

BCP is the tool you're looking for. Here's some links that should put you in the right direction: https://msdn.microsoft.com/en-us/library/ms162802.aspx https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/


0

You mentioned you could not use 'locking by deletion' because the row must stay in the table. The code example below does use it, but only after creating a global queue first and populating it. It then uses the 'locking by deletion' for the ##Queue table rather than the table you're working with. USE master GO -- 1. Create a Stored Procedure that ...


2

Apparently we were able to solve the problem. There is one particular UDF that is being called thousands of times per second, and it declares 3 table variables. For most of the calls, the UDF returns before declaring the tables, but I did not realize that SQLServer creates them anyway. We separated the second part of the query to a new UDF that is called ...


0

Found the problem, there were ties in the rank ordering, hence why multiple rows are returned with the same rank id. The solution will be for me to find an ordering key that has unique combinations of partition and order key.


1

I have tried to recreate your scenario on a test system using a simple stored procedure. The way I have approached this problem is as follows: Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'. Create a test stored procedure to be called by the 'BackgroundProcess' job. Create a 'BackgroundProcess' job that ...


0

The package itself is not failing, it is failing to Load. Either your package or it's configuration must have changed. Firstly, the error that is preventing your job from running is "The connection type "SPCRED" specified for connection manager "SharePoint Credential" is not recognized as a valid connection manager type." The SharePoint connector is not a ...


2

Let's start by considering the downpayments and payments together. WITH AllPayments AS ( SELECT * FROM TableB UNION ALL SELECT * FROM TableC ) Now get the total and latest date. , YearlyDetails AS ( SELECT TableAID, YEAR([Date]) AS TheYear, SUM(Amount), MAX([Date]) AS LastDate FROM AllPayments GROUP BY TableAID, ...


0

This can be achieved with a CASE..WHEN combination, with one WHEN for each column of the table in question. I use a temporary table to demonstrate the technique but it works just as well for normal tables. drop table #T; create table #T(PK int, c1 int, c2 int, c3 int); insert #T values (1,1,3,5); select PK ,case 5 -- The particular value you're ...


0

Assuming that the value you're looking for is 5, and it would be in an integer column in your table, you could use the below code. I added a toggle-able line, so if the value you're looking for is a string, you could search for it safely. Just quote out the + @val line and unquote the one below it, where @val is in QUOTENAME(). You would also have to adjust ...


1

Since you'd have to apply the differential backup's previous full backup, the end result is identical to if you'd just taken a full backup, made changes, then restored the backup you'd just taken - any changes made would be lost. There are, however, tools and techniques which would allow you to perform partial restores, so you could limit the application of ...


2

SELECT DISTINCT MAX(x) OVER (PARTITION BY y), y, when you're not returning any other columns is the same as SELECT MAX(x), y ... GROUP BY y. I'd suggest you start by doing that, and you may find your abilities are well capable of doing further refactoring.


0

Script to create read only rounting: /* set up read-only intent routing Test it: sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername" -Kreadonly sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername" */ ALTER AVAILABILITY GROUP [AG] MODIFY REPLICA ON N'node1.DevDomain.com \instance' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)); -- not ...


0

This might be the ugliest thing I've written in a long time, but it returns your results. SELECT Amount.Id, Amount.Name, Amount.Amount, MaxOrder.MaxDate FROM ( SELECT a.Id, a.Name, SUM(Amount) AS Amount, a.[Year] FROM ( SELECT Id, Name, SUM(Amount) AS Amount, YEAR(Date) as [Year] FROM TableA as a ...


0

You can use snapshot replication (it allows you to update the table every 5-10 minutes). If you would like instant changes, go for transactional replication. Note that you cannot publish a replication on SQL Server Express, you need at least Standard edition.


4

Is this a good candidate for transactional replication? I would use SSIS to incrementally load data (if the data set is too big) or just freshly load the data (if the data is less) every night (or anytime). This way you dont have to create any publication, distribution database or subscription. Easy to manage when you do upgrades on your main database ...


4

There are so many things outside of SQL Server's metadata that may depend on the size of a column, it's not even funny. Here are a few things you'll need to check: Any variable or parameter declarations in stored procedures, dynamic SQL or ad hoc SQL that may pass values to that column, filter on it, search from it, or get assigned from it. Any references ...


0

Given your situation and the information presented here, consider the following... If disk space allows for it, configure your custom log shipping to have 2 databases on the Secondary server that are both constantly restoring logs from the Primary. The purpose of database A would be for Disaster Recovery purposes and should be left alone so you never ...


0

You could use an extended event to capture the end of the job. Then have that initiate your desired processing, perhaps via Service Broker and Activation.


0

Found the answer. The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: - CREATE EVENT SESSION [Login] ON SERVER ADD EVENT sqlserver.login(SET collect_options_text=(1) ACTION( ...


0

(since this is longer than a comment .. so posting this as an answer) In sql server enterprise edition, you can leverage database snapshots. If I understand your question, you want to do some changes on a standy (not live) copy of your database and then discard those ? If that is true, then you can create a database snapshot of your main database using : ...


5

@AaronBertrand has a very good answer and is correct - it depends. I'd like to add that you're only looking at one metric - index fragmentation. That's a good start, but there are other things to consider. Consider the following scenario (using AdventureWorks2014): select * from Sales.SalesOrderDetail where SalesOrderID < 50000; --modify delete from ...


0

Okay, I am going to assume that we are talking about ETL operations for loading data into your data warehouse. One great option that you have available to you since you are using SQL Server 2012 is creating a SSIS Catalog and migrate your ETL jobs to be hosted there. This offers you tons of reporting and performance counters that you can use to track and ...


5

The big difference, in my environment, is that Reorganize is single threaded, and Rebuild can go as parallel as you have processors. Rebuild also has the added bonus of updating your statistics, so you can forgo separate statistics updates where you've rebuilt (unless you need to scan at a higher interval than the internal MS algorithm.). From my ...


8

Let me quote another part of that same page, which basically says IT DEPENDS - emphasis mine: These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best ...


1

Yes, it is still recommended not to shrink. If your data file goes up to 20 GB every quarter, then think about this: after you've shrunk it to 5 GB, you've freed up an extra 15 GB on the drive. What are you going to use that space for? Are you going to lease it out to someone temporarily, then kick them off the drive when your database starts to grow again? ...


5

What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to ...


0

Recommendation would always be not to Shrink: Because by going that option of shrinking the database there would be a request to SQL server to release the unused space which would cause index fragmentation and ultimately hamper the performance. If you're Database is a growing database it would still grow and shrinking is not the solution for that. Also ...


3

Shrinking database log file is necessary if log has grown out of control whether after a one-time data delete or where recovery model of the database is FULL and no log backups have been carried out. To reverse the effect of a one-time operation that caused unexpected log file growth are convincing reasons for shrinking a database\ database file. ...



Top 50 recent answers are included