New answers tagged

2

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests) Sql_handle is a hash value which identifies SQL text of the batch/query being submitted to the server. Text can be NULL for encrypted objects so this can cause sql_handle to be NULL


1

I too was experiencing a 5 to 10 second delay when right-clicking a table to pull up the context menu. My situation might be a little different than some however because I use local databases only. My solution: After reading the answer from imran about disabling antivirus, I took that concept a step further since I do not want to disable my antivirus ...


3

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored ...


5

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? It depends. No - if you run exactly the same queries as before (no access to the new data at all). SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree ...


1

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower? Wrong question. Yes, they will be slower - the index is deeper, more data must be accessed to filter. But the real question is: Will it be significantly or at least noticeable slower - and that is likely a no, because the index depth growth is NOT linear ...


6

That DONE_IN_PROC message from the execution plan is completely independent of your WHILE loop, and isn't what's being checked in your conditional BREAK. Martin deleted his answer but this is very much by design. Your query shouldn't behave differently depending on whether or not your client tool is retrieving execution plans - that would be bad. Execution ...


1

In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist). An easy fix for this is to move that condition to the on clause like this: select x.* from factI as ...


10

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and ...


2

If you want something to run on server startup you can create a stored procedure in the master database and mark it as a startup stored procedure as long as the scan for startup procs configuration option is set. No need for powershell or batch files. I have come up with one solution for which I am not sure - is it good or not? To determine whether ...


1

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your ...


4

Based on your sample data it looks like there are 6 columns in total. COL1, COL2, COL4, COL5, COL6 = All fixed width . COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding). You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed ...


2

I found the answer eventually .... There was a job set up to incrementally process the cube. Incremental cube processing requires a query to be defined to select new fact data to merge into the cube. In my case, this query was defined badly and was selecting the entire set of fact data, i.e. all the data that was already in the cube. Although fully ...


0

Do a Restore Headeronly With File = '...' If it still fails it's not a valid native SQL Server backup file. It's not an issue of versions. Open it with a hex or text editor, what's the file header look like? Maybe it's compressed with a 3rd party solution like LiteSpeed and those require you to use a tool to convert to native format if you don't have it ...


1

Run SQL Server Configuration Manager and under SQL Server Services find your SQL Express instance and see what the Log On As is set to. Alternatively, you can right click the server name in SSMS, Facets, then scroll down to "Service Account" to see it. If it's the default NT AUTHORITY\NETWORK SERVICE then grant access to a computer account called ...


0

Based on what you have shared, this should work: Table and data CREATE TABLE #Orders (Personid int, proc_code int, time datetime) INSERT INTO #Orders VALUES (123, 1, '4/25/2016 00:01:00') INSERT INTO #Orders VALUES (123, 2, '4/25/2016 00:01:00') INSERT INTO #Orders VALUES (123, 2, '4/25/2016 00:02:00') INSERT INTO #Orders VALUES (111, 1, '4/25/2016 ...


2

I would do it in this order: Change on Async Replica and restart SQL. Make sure everything is still synchronizing after restart Change Sync Replica and restart. Make sure everything is still synchronizing after restart Failover to Sync Replica Change on former Primary (Current Sync Replica), and restart. Make sure everything is still ...


0

According to this and this, there are upgrades to the sqlcmd.exe utility that I am assuming are reflective of underlying changes in the libraries. For instance, Beginning in the ODBC Driver 11 for SQL Server, there is no limit on the amount of data that retrieved in a single column when –y0 is specified. I realize that this says the ODBC driver, but ...


6

There is a bug that aggregates the time in a parallel operation. This is fixed in 2014. The total_elapsed_time will be correct for a particular parallel query in a batch until it moves on to the next statement in the batch, then the total_elapsed_time will explode by the DOP. Example Run this in one query window: USE AdventureWorks2012 GO SELECT * FROM ...


3

Indexes The decision to add one or more nonclustered indexes is an assessment only you can make accurately, based on the results of testing, and taking into account your local priorities. That said, the performance impact of adding a small number of narrow nonclustered indexes is typically small, from the database's point of view. More generally, the ...


1

So you want top 10 records per state by the date when record was inserted in DB. (I have assumed that you have date column for FCFS). You can use partition function for this. select <list_of_columns> from ( select ROW_NUMBER() over (partition by state order by date asc) as row, ...


0

In case of postgresql: You didn't write which version you have from postgresql. You can use LATERAL JOINs (available from 9.3) or window functions. Example for window function: select *, row_number() over (partition by category_id order by [whatevercolumn]) as rownum from records r join category c on c.id = r.category_id where c.id IN (1, 2, 3, 4) -- ...


5

It looks like it could also be an bug/issue with the DMV. There is a Connect bug report here of this same kind of inaccuracy. The suggested workaround is to use GETDATE() - sys.dm_exec_requests.start_time instead of total_elapsed_time. The issue is resolved in SQL Server 2014. To quote the comment on the Connect item by "Nathan (MSFT)": The issue ...


4

Summary The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type. AdventureWorks-based examples Download the Microsoft sample database here. Table type CREATE TYPE ...


0

Copy data from more than one table into the new table: SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2013 FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID; Should be something like this for your data set. SELECT Movie.MVNUMB ,STAR.STARNUMB INTO MOVSTAR FROM Movie INNER JOIN Star ON Insert the two ...


8

You actually have 595,947 matching rows, which is about 3% of your data. So the cost of the lookup adds up quickly. Suppose you have 100 rows per page in your table, that's 200,000 pages to read in a table scan. That's a lot cheaper than doing 595,947 lookups. With the GROUP BY clause in the question, I think you'll be better off with a composite key on ...


2

O.K. lets create a nice example for you and show you how to script the index of your indexed view. first, lets create a temp table called T1 and add some meaningful data into it lets create a procedure that generates random strings first: USE TEMPDB GO CREATE PROCEDURE [dbo].[SpGenerateRandomString] @sLength tinyint = 10, @randomString varchar(50) OUTPUT ...


5

The field in your WHERE condition is not the leading field of the index. You have measure defined as NVARCHAR so prefix the literal with an N: where Measure = N'FinanceFICOScore'. Consider creating a Clustered Index on SnapshotKey. If it is unique then it can be a PK (and Clustered). If not unique then it cannot be a PK, but can still be a non-unique ...


12

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here. In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID ...


1

I would investigate building a .Net application that makes use of SQL Server Query Notifications to invalidate the cached data, and repopulate it from the source data. From the MSDN page linked above: Query notifications are used for applications that need to refresh displays or caches in response to changes in underlying data. Microsoft SQL Server ...


1

SQL Agent jobs are not contained within the Availability Group. When using SQL Agent jobs to manipulate a database within an Availability Group, there are all sorts of special considerations that need to be taken, particularly related to failover. What happens if the job is running during failover? The job will likely fail, as the replica it is running on ...


-1

Please check the disk space or you may need to check the running user account on the present SQL Server instance. See my blog post: Ways to Fix SQL Server Error 3201 - Cannot Open Backup Device


6

The easiest way would be to add a computed column to the table with your mod calculation, and then create an index or add it to an existing index. For example: -- Test table and values CREATE TABLE #Test (col integer PRIMARY KEY); INSERT #Test (col) VALUES (1), (2), (3), (4), (5); -- Original query (scan) SELECT T.col FROM #Test AS T WHERE col % 5 = 4; ...


2

The terms are being confused sadly which is causing an issue. There's WCFS (Windows Clustering Foundation Services) which FCI (Failover Cluster Instance) runs on and requires shared disks. An AlwaysOn AG can also run on WCFS but it uses non shared storage and provides live read only replicas. In other words A FCI is a shared storage architecture and you ...


2

This worked for me: EXEC master.dbo.sp_addlinkedserver @server = N'TestConnect' , @srvproduct=N'' , @provider=N'MSDASQL' , @provstr=N'DRIVER={SQL Server};SERVER=TestConnect;Trusted_Connection=no;' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TestConnect' , @useself=N'False' , @locallogin=NULL,@rmtuser=N'ReportReadOnly' , ...


0

I have gone through MSDN BOL blog How to backup SQL Server databases to a mapped drive and run the below query. Run the following command from a query window EXEC xp_cmdshell ‘net use <drivename> <share name>’ After that i am able to take backup in Map Drive. where drive name : Letter used to map the drive share name : UNC path to the ...


3

SQL Server can only see network drives that are mapped in the Windows Profile of the SQL Server service account. So you'd have to log on as that account (not your own) and map the drive, for SQL Server to be able to use it for backups. Backups run using the credentials of the SQL Server service account, not yours. As suggested in the comment above, try using ...


1

Scalar UDFs always affect performance. What you might have read is that you probably don't want to use them in your WHERE or JOIN ON clauses because that test is happening as part of the filtering stage and thus may force every single row to go through the UDF to determine the output. This is compared to the less worse option of just using it in the part of ...


0

I've checked a couple servers and on the background requests observed a drift of about 14s over 2 months. However that aside, the only significant difference I can see on other requests is where the spid has gone into a SLEEPING state. I suspect that this value does not increment while in that state; but I haven't been able to force a query into SLEEPING in ...


3

No. Each CU contains all the fixes in the CUs prior to it. For the second part of the question, the only RELIABLE way supported across most versions (2005+) is to get a 4 part number and compare it to any of the comprehensive build lists available on the internet: Select Serverproperty('ProductVersion') You can also check @@Version but it's not ...


1

I wouldn't organize your "helper table" that way. I would keep only {Region, Salesman, StartDate}. To create a view of intervals, join that table to itself to create data ranges where a.StartDate < b.StartDate. Use an outer join, so that NULL represents an EndDate of "now", which you can coalesce with the current date or other, as appropriate. To ...


0

Reading and writing a lot to a table can be hard to to keep performant. Indexes could indeed hurt insert performance and locking is also an important aspect to keep in mind. There is another way to fix those problems - don't write directly to the database. For example, write the messages to MSMQ and use a Windows Service to insert them (batched) to the ...


1

Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem. If the server hosts a data warehouse or reporting type of database that receives a low volume of queries but processes large amounts of data, ...


0

I would just go with a bigint with Identity column. Just remember to start it at the negative seed value since sql server does not support unsigned ints. Something like this... CREATE TABLE Records ( RecordID bigint IDENTITY(-9,223,372,036,854,775,808,1), RowLabel varchar(255) NOT NULL, CatLabelID INT, StartPosition varchar(255), EndPosition ...


3

Do the percentages and query cost even mean anything? Not really--They have meaning, but they're unreliable. are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Exactly--The cost is ...


2

Presuming the database is not actually "in-use" at the time this rebuild process starts Assuming what you are presuming, I don't what side-effect it could have, outside of the obvious allowing for index REORG to happen :-). Of course, I have no proof, but not sure what proof to look for that nothing bad will happen when nothing is happening in the DB ...


1

You can find a more information on Kerberos from the CSS team here. As well Microsoft released a tool that can help you identify Kerberos issues, download that here. To troubleshoot, check your error log that shows the startup of your instance after you changed the service account to that new domain. In a perfect world you will see a message like this: ...


1

The counter Memory Manager: Free Memory (KB) tells you below. Read the BOL document Free Memory (KB): Specifies the amount of committed memory currently not used by the server. So what this means is of all committed memory SQL Server is not using 40 MB of the memory. This is not telling you free memory on the SQL Server system. A committed memory is ...


2

Restart SQL Server and see if they suddenly match. There is a documented behaviour where if tempdb grows you can get different results depending on how you query what size it is. For example sys.master_files shows tempdb size on start; sys.database_files shows actual size (as do some of the Fileproperty() function results). Nacho Alonso Portillo wrote on ...


0

I would first try running a below command to see if that fixes the issue. EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1; If not, I will create a publication for a single article and then run the above command again. This should mark all the replication transactions as complete. Once I am able to shrink the ...


3

SQLCLR is the ability to run .NET code within SQL Server. When people speak of SQLCLR they are usually referring to the ability to write custom .NET code (Stored Procedures, Functions, Triggers, User-Defined Types, and User-Defined Aggregates). In this case, this ability can be turned on and off via the server option of "clr enabled" within sp_configure, ...



Top 50 recent answers are included