New answers tagged

1

Finally figured this out! We reference many of our very active SQL Servers and Instances with SQL aliases and canonical names. This allows us to perform parallel upgrades and switch machines in and out without code interruption. The SQL aliases are all configured to reference the canonical names for the same reason. The standard SPNs we had registered ...


0

You should consider using LEAD and LAG Analytic Functions and appropriate indices for sorting. I've changed one self-join query with them. Elapsed time was reduced tenfold.


0

I don't really follow the examples but based just on: Each time an event of type=0 happens, a counter increases for the item involved, and each time an event of type=1 happens, it decreases again. The counter must always lie between 0 and 3 (inclusive). Easier if you just change event_type to -1 and 1 select item, min(seq) from ( select ...


5

It's actually usually referred to as SQLOS and it means SQL Server Operating System. Details in this series Also it can be found referenced in documentation in some places, e.g. here: [...] associated with the SQL Server Operating System (SQLOS). The SQLOS is responsible for managing operating system resources that are specific to SQL Server.


3

This has nothing to do with object name reservations in TempDB or anything to do with runtime. This is simply the parser not able to follow logic or code paths that assures that your code couldn't possibly try to create that table twice. Notice that you get the exact same (non-runtime!) error if you just click the Parse button (Ctrl+F5). Basically, if you ...


-1

Why don't you use Database Mirroring instead and configure your applications to connect using a fail-over partner in the connection string (read more about that here Connect Clients to a Database Mirroring Session (SQL Server)). If you require automatic DBM fail-over then you will also need a Witness server (and run in-sync) in order to use High-Safety Mode ...


0

The reality is that SQL Server does not have a read-write scalability story in the same way that Oracle does in RAC so whatever you choose, you will always needing to consider that data-sets in one place or the other could be slightly behind. Availability Groups are probably the easiest solution to manage but then the only thing you are scaling out is ...


3

Having an additional log file doesn't actually hurt performance in and of itself. That doesn't however mean that you won't have performance issues when you have multiple files. If one of your files is on slower storage for example you could end up with intermittent performance issues which can be difficult to troubleshoot and cost you quite a bit of time. ...


0

In our experience peer to peer replication is simpler and more robust than earlier forms. A solution based on Service Broker would enable asynchronous distribution. The application would have to be resilient to the various combinations of CAP scenarios. Master Data Services supports publishing consistent sets of tables. The cadence may not suit, though, as ...


0

Its possible to add more than one log file but it doesn't do any good. In fact, it generally hurts performance and just adds overhead (plus anyone that does so ends up looking like they have no idea about SQL Server).


0

One approach if you can't use a partitioned view is INSTEAD OF triggers like the examples below. Note that the UPDATE trigger assumes that one never changes a primary key value after the initial insert. CREATE TRIGGER [TRI ALL COMPANIES$Employee] ON [ALL COMPANIES$Employee] INSTEAD OF INSERT AS INSERT INTO [Company 1$Employee] (Col1, Col2, Col3) ...


-1

@Adam,I would like to say that as your question like that There will be one publisher and multiple subscribers. Is it possible to define that synchronisation is synchronous only for one subscriber and for the other is asynchronous operation. Yes , it is possible through Transactional Geo replication. Replication to SQL Database Here I am also ...


-5

At that point I decided to use DDL triggers, which unlike the DML, have not a significant impact in terms of performances. First of all we have to understand which events intercept, using this query you can have a hierarchical view of available events: WITH EventsReports (name, PARENT_TYPE, type, level, sort) AS ( SELECT CONVERT (varchar (255), ...


0

As described in previous answers - After indexes are created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data. Even so, these automatic modifications will continuously scatter the information in the index throughout the database – fragmenting the index ...


0

There are no such things as "alternate keys" and "secondary keys" (they're just nick names), only a Primary Key and a Unique key. Microsoft explains about the Primary Key here. This creates a unique index to enforce the uniqueness. The columns cannot be NULLable. Microsoft also has a Unique key (see here). This is almost the same as a unique index. This ...


1

This query is run by Management Studio. I have checked this Microsoft SQL Server Management Studio(13.0.11000.78). In recent releases ability to connect to Azure SQL Database been added to SSMS and looks like SSMS executes this query when ever we change focus to any tab or click in SSMS tab. So now when we switch table from session 1 (which creates ...


0

You will need the ADO.NET and SQLNCLI11 fix for your drivers to make them work with TLS 1.2. As of January 29th, Microsoft SQL Server supports TLS 1.2 for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and major client drivers like Server Native Client, Microsoft ODBC Driver for SQL Server, Microsoft JDBC Driver for SQL Server and ...


5

The greatest-n-per-group tag has a number of questions and answers relevant to this type of problem, with the canonical example for SQL Server being: Retrieving n rows per group With the two main options being: ROW_NUMBER (as in Aaron's answer); and APPLY So while the question is most likely a duplicate of that (from the point of view that the answer ...


1

Do you need to handle this directly in the database? My inclination, since you're ok with slightly stale data, is to cache individual query results, rather than the table as a whole, in a layer like memcached or redis. This is a pretty standard approach in web application development. The primary downside is that it requires development effort on the ...


1

An alternative you might consider is nested groups, such as this. select tt.Id, tt.EffectiveDate, tt.SequenceId, tt.CustomerId, tt.AccountNo from dbo.TestTable tt join ( -- Find maximum SequenceID for each maximum EffectiveDate for each Id select it.id, it.EffDate, max(t1.SequenceId) SeqId from dbo.TestTable t1 join ( -- Find maximum ...


2

Audit Add Role Event is actually auditing the addition of a new role (e.g. CREATE ROLE). To audit adding a new member to an existing role, you want either 108 - Audit Add Login to Server Role Event or 110 - Audit Add Member to DB Role Event, depending on which type of role you actually mean. Here's an example of each: Database user added to database role: ...


2

As Aaron has already said, this is due to a bug in SQL Server 2008 / 2008 R2. The bug has been marked as fixed in a later version. It will not be fixed in SQL 2008 / 2008 R2. I have a PowerShell script that I wrote to pull deadlocks from the event buffer, add the missing information if possible, and write the deadlock graphs out as .xdl files. The .xdl ...


3

I can't tell you why with the information provided, but I can give you some guidance to figuring it out. Monitor your wait statistics while the longer job is running. You should be able to figure out which session is running the backup, see what it is waiting on. sys.dm_exec_requests will tell you this (check the wait_type, and also look at ...


6

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag): ;WITH cte AS ( SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo, rn = ROW_NUMBER() ...


1

Hard to suggest a more specific fix or suggest a cause given that we have no background on how your system got into this state, what you did prior to trying to create the first diagram, if you have successfully created diagrams in the past, or even what version of SQL Server you're using. It sounds like your sysdiagrams table somehow lost the IDENTITY ...


2

I think you made a leap. If you change model to simple recovery, the log won't autogrow constantly, because that allows the log space to be re-used (and it is unlikely there is anything going on in model that would prevent that from happening). But, I don't know how you're using model. If it's being used to store objects and data that you want created in ...


0

I was able to find pages explaining how Maintenance Plans write and how to query them. Between that and a discussion on how to use PowerShell to query XML, I think I have what I need to pull the maintenance plans in and query them. Since the SSIS package is essentially an XML document, I'm going to need to dust off (and tune up) my XQuery but here's what I ...


-3

15K in 5 minutes 50 / second - that is reasonable The likely culprit is page splits and index fragmentation Give this a try On the insert sort by the order of the PK on the insert Put a fill factor of like 50 on all the indexes Try an insert If this work then tune the fill factor and add in index maintenance Maybe even tune the order of PK to put ...


-2

If you are using INSERT INTO statement try to enclose everything into a single transaction block: Something like this for example: declare @i int set @i = 0 set nocount on BEGIN TRANSACTION while @i < 2000 begin insert into testdb(testcolumn) values (1) set @i = @i ...


4

Without more detail about the procedure[1] and the table it is inserting into we can offer any specific help. Some general advice though: If you are inserting the rows individually, try arrange it so the are inserted one large blocks, all at once if possible. Confirm that the procedure is in fact being slow due to its own activity rather than waiting for ...


0

You should set MAXDOP and "Cost Threshold for Parallelism" according to best practices. MAXDOP should generally be set to the number of cores per NUMA node. See my question here for more details. Cost Threshold for Parallelism should likely be set to at least 50.


0

Purely from a hardware perspective, manangement of main memory form the Nehalem architecture onwards is management by an integrated memory controller, this is on the "Un-core" part of the CPU die which is seperate from the part which the actual cores live on, because memory is effectively 'Wired' to each CPU, foreign memory access AFAIK is via the quick path ...


0

Manangement of main memory form the Nehalem architecture onwards is management by an integrated memory controller, this is on the "Un-core" part of the CPU die which is seperate from the part which the actual cores live on, because memory is effectively 'Wired' to each CPU, foreign memory access AFAIK is via the quick path interconnect ( again from Nehalem ...


0

Some of your question is kind of confusing to me so I am asking some clarifying questions. It looks like you tried to hide your server and instance names in some of the messages, but did not in other places. From the PowerShell error message it shows you were going after a server named 'serverr2' and the instance name of 'serverr2'. My first thought is you ...


2

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator ? Conditional One way to implement the Derived Column would be to parse the string like this TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) ) In this case, "name" is the column that holds ...


1

This isn't in the expression language (it's T-SQL) but you should be able to use this as either a pattern or to pull the data from SQL (if that's where it is). CREATE TABLE #test (Col1 varchar(100)) INSERT INTO #test VALUES ('\\Server\Share\named resource'), ('\\Server\Share\this.looks.like.a.file_but.it.isnt\its_a_directory'), ...


0

-- disable all constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in *ALL* tables (not just refTables) EXEC sp_MSForEachTable "DELETE FROM ?" -- reseed the indexes so primary keys start over at 1 declare @cmd varchar(4000) declare cmds cursor for select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);' FROM sys.tables ...


2

I've always done this by making a step's next action on failure be a specific step that sends an e-mail about that step only, then that step's on complete action is to continue to {failure step + 1}. Yes it requires some manual interaction with the jobs up front, but I can't think of any way to change the defaults without some level of manual tinkering.


2

Short answer: No, you cannot partition by a column in another table, because that would cause problems whenever you change something in the "other" table - rows would magically have to move between partitions. Summarizing some of the views in the comments from Aaron, Kenneth and me on your question: You could change AccountMonthId from a sequential number ...


0

A transaction like Kenneth Fisher answered And look at optimizing Why are you updating after the insert? Why not just insert a hard coded TargetAmountSetTable.isModified of 1 in the insert? Another option is to output the whole lot of joins once to a #processTable


2

In general what you want here is an explicit transaction around your code. BEGIN TRANSACTION ... your code .... COMMIT However there are down sides to concurrency here as well. While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction. That means that the next set of code that needs to run will ...


1

1000 transaction per minute = 16.67 / second = 480,000 / 8 hr day 16.67 / second is not the fast. I am getting over 100 / second on just a regular active big table. Pick your PK or at least one index that you can sort the incoming data by so you have minimal fragmentation of that index. If you can hold records to insert 100 or 1000 at a time and ...


0

Yes, it is necessary to drop and recreate the linked server. This takes just moments, so the hit is very small. Likewise, there is no reason to believe that SQL Server would try to read the internal contents of a different relational database.


1

As an alternative, if you have Solr already set up, you can have it index your document and return it to you in multiple formats (JSON, XML, even Python dictionaries). Solr offers a way to search through your information really fast as well as allowing you to do faceted queries as well. While creating your own denormalized structure in a RDBMS would ...


0

This query get the first Approve and first Not Approve: WITH list AS ( SELECT ea.TaskId, e.EmployeeName, ea.IsApprove , n1 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove DESC, ea.Id) , n0 = ROW_NUMBER() OVER(PARTITION BY ea.TaskId ORDER BY ea.IsApprove, ea.Id) FROM @Employee_Approval ea INNER JOIN @Employee e ON ...


2

Use a Derived Table/CTE to get a single ID for "approved"/"not approved" and LEFT JOIN it two times to Employees: SELECT TaskID ,MAX(CASE WHEN IsApprove = 1 THEN EmployeePermission end) AS Approved_ID ,MIN(CASE WHEN IsApprove = 0 THEN EmployeePermission end) AS Not_Approved_ID FROM "Employee Approval" GROUP BY TaskID


6

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance ...


2

If it's just a single table, think about the idea of having a second copy of the table used just for reporting purposes. I wrote a two-part series about my solution here: Part 1 | Part 2. Essentially you have a table that represents a copy of your transactional table, but it is optimized for your reporting workload (as such, perhaps it only has a subset of ...


0

How you solved the issue is the way to solve it because we can't modify a Linked Server. And SQL Server doesnt read the tnsnames.ora. Though the solution is drop and recreate we can use SQL Management Studio to edit the Drop and Create script as follows: 1. Right click on the linked server. 2. Choose Script Linked Server as. 3. Choose New Query Editor ...


1

I think Daniel's answer is probably better than mine, but just to give you the basic alternatives: Transaction replication with only that table replicated, to a different server. Pros: Instant, readable data Read locks will only block the replicated server Transactional replication is read through your transaction log, with an Agent reading all ...



Top 50 recent answers are included