Tag Info

New answers tagged

1

For the portion of the question asking about compression, Dave is correct on the trade off. I would investigate adding more capacity to your system before you head down the road of compression. For the partition portion the answer is going to be it depends. Partitioning takes a lot of planning and analysis of your current queries before you can measure ...


2

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc. Due to heavy blocking, we can't purge data while the database is online. Table partitioning is ...


0

A couple things I noticed about the query that if you can get them changed may help in performance Replace the datediff method with a variable that is set with the dateadd method to add 15 days to today's date. This could allow the query optimizer to generate a plan that can go parallel. Since you are using the exists method in the where clause specifying ...


2

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist. If you haven't already, run the restore command with the verify only option MSDN. If that is successful here are a few additional things you can try to isolate why it is failing If you have the space on the ...


1

A quick-and-dirty way to get this information is to run something like this query while the query you wish to observe is still running. select st.text, r.wait_type, r.wait_time, r.wait_resource, qs.creation_time, qs.* from sys.dm_exec_query_stats qs join sys.dm_exec_requests r on r.sql_handle = qs.sql_handle cross apply ...


2

You forget group by account_no. So finaly: select account_no, 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) from accounts etc group by account_no HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4


6

Try: HAVING 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4 However you will still probably find that integer math is preventing you from getting the result you want. So this might be better: HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4


1

I love Aaron Bertrand's answer. Although I don't understand it completely, it looks really elegant. In the past I've ran into problems with permissions when using sys.objects. Combined with the need for me to troubleshoot the code, I've come up with a variation on Aaron's code, and added it below. This is my procedure: CREATE PROCEDURE ...


3

You are looking at the wrong output. The output you are looking at is the results pane. If you look in the messages pane you will see that you are in fact printing the first row twice. In both cases it's because of your second fetch FETCH NEXT FROM idCursor You have to tell it the variables you want to put the data into. Change it to look like the ...


2

There are two possible explanations: Your code looks like this: DECLARE c CURSOR FOR ... OPEN c; FETCH c INTO @var; -- fetches 1st row WHILE @@FETCH_STATUS = 0 BEGIN FETCH c INTO @var; -- fetches 2nd row -- do something END If that's not the case, then since you don't have an ORDER BY, SQL Server is free to return the results in whatever order it ...


1

This totally depends how healthy the server is SQL1,2008R2. You will need to analyse first the usage of Databases, SSIS or SSRS and other parameters hosted on the server. Also, what resources you have in terms of RAM, CPU, Disk(Raid levels) etc. available on the server along with how they are being getting used. It would be a good start if you separate ...


0

Well, I think this is a good example when SET operations might not be the best tool in the box. I'd go for a simple cursor here! :) Both easier to read, maintain and scans the table only once! An RDBMS does not really have the concept of 'ordered table' unless you explicitly specify it with an ORDER BY. I do, however, guess your table is 'ordered' by < ...


0

You can detect any ddl changes by using trace file. below is script to get changes. SELECT te.name AS eventtype ,t.loginname ,t.spid ,t.starttime ,t.objectname ,t.databasename ,t.hostname ,t.ntusername ,t.ntdomainname ,t.clientprocessid ,t.applicationname FROM sys.fn_trace_gettable ( CONVERT ...


0

You can use Sql Server Data Tools (SSDT). Microsoft Visual Studio allows you to create a Sql Server project. One then imports the database into the project and then build the project. If there are any broken stored procedures or objects, you will get a compile error.


2

Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try: SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0); As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.


0

There's two forms to audit a SQL Server The SQL Profile: https://msdn.microsoft.com/en-us/library/ff650699.aspx And DDL Trigger's https://msdn.microsoft.com/en-us/library/ms189799.aspx


1

You will almost always be susceptible to vendor updates when it comes to schema changes and their app, especially when it comes to ERP applications such as SAP Business One or it's bigger brother. The issue is how will their update scripts react to your table being added/modified? Usually it's not a big deal for me but sometimes it could cause massive ...


24

This pattern column = @argument OR (@argument IS NULL AND column IS NULL) can be replaced with EXISTS (SELECT column INTERSECT SELECT @argument) This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article: ...


-3

UPDATE TABLE_NAME SET ToDateTime = ContainedData WHERE AID = 123 AND PID = 2


2

What I've been using is Adam Machanic's Who_IS_ACTIVE to track the stats for slow running queries. The prod uses the DMV's and will track the stats as mentioned. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect ...


2

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages... something like this, which uses the language setting to control isdate and convert. set language english; update test_dates set ToDateTime = convert(datetime,ContainedData) where AID='123' and PID='2' and ...


2

How about this: UPDATE [dbo].[MyTable] SET [ToDateTime] = [ContainedData] WHERE aid = 123 and pid = 2 and isdate([ContainedData] ) = 1


3

You just need to specify the first SiteInfo node using [1] update [dbo].[Sites] SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])') where Siteid = 1


1

Microsoft has a MSDN document that explains how to start the SQL service in single user mode that will allow you to connect as part of the sysadmin built in role to add users back in. https://msdn.microsoft.com/en-us/library/dd207004(v=sql.100).aspx The 2008 version doesn't have as much detail as the 2014 version but you should be able to use reference ...


1

You can try below alternatives: If you installed SQL Server then you will be having "sa" - if mixed mode authentication is enabled. You can use "sa" account to create a login and map that login to a database. Alternatively, you can use "DAC" (if you have enabled it as a part of post installation step). By default, BUILTIN\Administrators has access to sql ...


1

This is one that all SQL DBA from old time use. EXEC sp_help 'your table name'; If only with single line, you don't even need to type EXEC. Just do sp_help 'your table name'


1

There are plenty of differences. One that comes to mind right away is the MERGE statement, which first became available in 2008.


2

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks. DECLARE @qry NVARCHAR(MAX) = 'SELECT ' + STUFF(( SELECT ', ' + QUOTENAME(c.name) FROM Report.InfoColumnOrder r JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info') WHERE u.UserId = @user ORDER BY r.SN FOR XML PATH(''), ...


2

There are several factors that are contributing to your slow performance. Using the like with the wild card on both sides of the text is going to cause the query to scan all of the rows in both tables multiple times to match your join predicate because the expression is not SARGable. The LEFT operator in your where statement is also going to force your ...


0

Using wildcards for data that "they don't necessarily exactly match" seems likely to cause you a lot of grief. A search that begins with '%' will turn that into a table scan since no index can be used in searching the names. From your comments you apparently recognize that this is a thorny problem and you are unlikely to get perfect results. Since you are ...


-1

although this is answered I just though it would be nice to drop this little line of code that does exactly the filtering most people want and sets AUTO_SHRINK OFF: EXECUTE sp_MSforeachdb 'IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model'')) EXECUTE (''ALTER database ? SET AUTO_SHRINK OFF'')


3

Please use Database Mail instead of SQL Mail. SQL Mail had security issues on several layers and in SQL 2005 SP2 had 'inbound emails' removed. They then further removed support for it and it is now in the depreciation list. Database Mail is the solution you are looking for. It passes audits easily, has lots of documentation, and is fully supported by ...


1

Check if the SQL Server user account is able to read from "\Mylocation\DBRestores\", the security permissions from file share and file system. To really test that it works, copy the backup to local server and run it from there.


0

This part of your SQL is incorrect DISK = N'\Mylocation\DBRestores\MyBackupFile' it should be for example DISK = N'\Mylocation\DBRestores\MyBackupFile.bak' you are supplying a directory but should be supplying the full spec of the backup file.


2

No application should require the SA account and password to operate. However, I have installed an IT Service Management product, and during the installation process you have the option to supply the SA account credentials to allow the installer to create the DB and attach an account to the DB for the software to use. The SA account credentials are not ...


0

Yes this is possible, but is not a good idea. You can theoretically run a web application anywhere and punch firewall holes so that the web application can access a database on prem. This opens up a whole lot of issues as the database will have to be on a secure network segment and can wreck havoc on security policies. You can make this more secure by using ...


2

You can also look at the application event log. For SQL 2008 R2 instances, the event ID to filter on would be 8957 (at least for success codes).


3

Method 1: Easy way The Best way that i am using as of now is ola hallengren's SQL server Maintenance solution for SQL Server Integrity Check. Within this solution it gives you an option of storing the output or logging it to a table as per you're convenience: LogToTable Log commands to the table dbo.CommandLog. Please read the complete web link as ...


4

{ This is a little lengthy, perhaps, but your actual problem(s) cannot be solved by looking at execution plans. There are two main issues, and both are architectural. } Distractions Let's start with the items that are not your major problem areas. These are things that should be looked into as it definitely helps improve performance to use the datatypes ...


4

In what situations is varchar(max) preferred Commenters have addressed this point in detail already. I'd say that VARCHAR(MAX) generally makes sense if you are 100% sure that the column will never need non-ASCII characters and the column's maximum length is either unknown or greater than 8,000 characters. You can read ...


0

That is the union of three sets. So the following should work (I didn't test it) select D3id,D2id,'D3' from mytable union select D2id,D1id,'D2' from mytable union select D1id, null,'D1' from mytable The union-operator automatically removes duplicates.


4

A CROSS APPLY would seem perfect for the job: SELECT v.Nodeid, v.ParentNodeid, v.Header FROM dbo.atable CROSS APPLY ( VALUES (D1id, NULL, 'D1'), (D2id, D1id, 'D2'), (D3id, D2id, 'D3') ) AS v (Nodeid, ParentNodeid, Header) ; For every row of the source dataset, CROSS APPLY produces three, using the VALUES row constructor, ...


1

Something like this would work: declare @cmd varchar(500) select @cmd = 'osql -U -P -S -Q"select * from myTable" -o"c:\output.txt" -w500' exec master..xp_cmdshell @cmd You could also do it by creating a C# based stored procedure and execute it via the CLR.


0

"result to text" is for client application e.g. in SSMS when you press CTRL+T, you tell the client application (SSMS) to show / render the results of the query in TEXT format. Similarly, CTRL+D in SSMS will show the results of the query in GRID format.


3

No, how the results are displayed (if at all) is the job of the client application.


10

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML). When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you. The provided execution plan shows the optimizer choosing a static-like plan. Because the ...


0

Actually I had to replace the log file to another drive which has space. Then I was able to delete the things and shrink until I freed space in DATA drive.


0

Open SQL Server Management studio 2008. Expand the server instance > Security > Logins > and locate the log in you have having an issue with. Right click the user and select Properties. Select User Mappings. Locate the database you want the user to have access to and check the checkbox in the map column. Set the correct database membership for this user ...


2

You need to delete in smaller chunks using a loop and TOP/ROWCOUNT. I have no idea what your magic number would be, so you'll have to play with it, but the point is to cause minimal usage of the transaction log and, in simple recovery, issue multiple checkpoints in between iterations of the loop so that the log space you've used can be marked for reuse. (The ...


1

Deadlock happens when one query acquires a lock on a object (rows, data pages, extent, tables etc) and other resource tries to access it. Smallest unit in SQL Server is data pages and SQL holds a lock on page while working on it. So, yes it is possible that two select statement can create deadlock. Solution: WITH(NOLOCK) - If your data is fine to be read ...



Top 50 recent answers are included