Tag Info

Hot answers tagged

9

First things first: How much data is there in the table? Number of rows and size of the table? Second: Can you back up and restore this table to a test server and run the alter statement to see the impact (assuming it is not unfeasible due to the table being too large to fit on a non-Production system)? I always find that testing in my environment is more ...


9

The query optimizer has a number of choices when constructing an execution plan for this query. Among the many strategies available, it can choose between hash join and nested loops join. Which one it decides to use depends sensitively on the statistical information available, and other factors like the amount of memory configured for SQL Server to use. It ...


7

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind. Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have ...


7

You need to use NCHAR(1 - 4000) or NVARCHAR, either as NVARCHAR(1 - 4000) or NVARCHAR(MAX) for storing anywhere from 4001 to just over 1,073,741,822 characters (or possibly less if storing any supplementary characters as described below). You will also want to specify a Japanese collation so that the data compares and sorts as expected. You can find the ...


5

Taken from MSDN:- https://msdn.microsoft.com/en-us/library/ms189628.aspx Page life expectancy - Indicates the number of seconds a page will stay in the buffer pool without references. SQL always looks for data pages in memory. If a data page is not in memory SQL will have to go to disk (perform a physical IO operation) in order to retrieve the data that it ...


5

In SQL Server at least (can't speak for the other RDBMS you've mentioned, sorry), a single statement will scale better than multiple, to a point. You can test this yourself using your exact statements and data, of course; nobody here can test that for you with your specifics, and your specifics may tilt things one way or another. "Which is faster, x or y?" ...


4

You should never try to update system tables directly, in most cases it is not going to let you as you have found. In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query ...


4

For SQL Server: Neither. To insert a lot of rows you should use bulk insert APIs, and thrive to achieve minimally logged insert. Bulk inserts can be achieved using IRowsetFastLoad (OleDB), using the Bulk Copy Functions (ODBC) or using SqlBulkCopy (.Net). All these APIs have in common that they establish a fast insert pipe with the server, and then they ...


4

An open-ended range is the safest in terms of granularity and sargability, and being immune to underlying data type changes: DECLARE @today DATETIME; SET @today = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE()),'20000101'); SELECT ... WHERE ordDate >= DATEADD(DAY,-1,@today) AND ordDate < @today; You may get suggestions for other ...


3

The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled). So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or ...


3

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time. Edits: I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them. The process was very ...


3

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind. You can say: EXEC dbo.myprocedure @foo = 'bar'; But you can't say: EXEC dbo.myprocedure @foo = 'b' + 'ar'; You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a ...


3

Have you tried: select Name, max(Mark) from Students group by Name


2

One approach would be to use optimistic locking. Before displaying the update form, you retrieve all the original values from the database. Or, at a minimum, a column that is guaranteed to change on update, such as last_update_date or similar. When updating you check that all values are still the same in your where clause. If the update fails to update any ...


2

Use Microsoft Performance Analyzer or Media Experiance Analyzer with Windows Performance Recorder. Any of these is capable of answering your question in great detail, quickly and foul-proof, using system ETW capabilities. This article is a good walk through: Analyzing Storage Performance using the Windows Performance Analysis ToolKit. At the simplest, it ...


2

A filtered index (WHERE IsMostRecentRun = 1) sounds like a better idea to me than using sparse. If you can make it so that false is instead represented by null, you may be able to do both, but while that will potentially save some space in the base table, I suspect the bigger gain would be in query performance from the filtered index - as long as it's ...


2

You need to use a derived table or a CTE. Using derived table: SELECT A, SUM(B) AS SumQuantity, MIN(D) AS MinPrice -- etc FROM ( SELECT CountryCode + ProductNumber + StockType AS A, Quantity AS B, Description AS C, Price AS D FROM dbo.vwSalesInvoiceWHandling UNION ...


2

Page life expectancy is a measure of how long you can expect a page that has just been read from disk to hang around in memory before it is pushed out by something else or is destroyed (i.e. that page is deallocated on disk to there is no need to keep a copy cached in RAM). As a general measure, the higher it is the faster your load pattern will be ...


2

I coded a script to ship logs manually using PowerShell. You can find it here Basically it does what native Log Shipping does: it takes transaction log backups, copies to a shared folder and restores to the secondary WITH NORECOVERY. Some limitations apply: Only a single secondary is allowed No support for STANDBY I coded it this way because it was ...


2

Native functionality, no. AlwaysOn Availability Groups have the containment of database. Server objects (Agent jobs, logins, alerts, etc.) just simply don't play a HADR role with availability groups. Whenever you run into the need to ensure that server-level objects exist on all replicas, or that they only execute on [particular_replica] then you will ...


2

sys.sql_modules isn't going to help you - you won't know which one is causing a specific update, or who's calling it. Here's a start - create a logging table: CREATE TABLE dbo.UpdateLog ( EventType SYSNAME, Parameters INT, EventInfo NVARCHAR(MAX), EventDate DATETIME NOT NULL DEFAULT GETDATE(), AppName SYSNAME NOT NULL DEFAULT APP_NAME(), ...


2

Use the performance counters Transactions/sec and Write Transactions/sec documented here: https://msdn.microsoft.com/en-us/library/ms189883.aspx


2

I just took a look at my ssms. Copy is in SSMS 2012 and 2014 under tasks. I haven't tried it but it is there. What ssms are you using? SSMS 64 bit: http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLManagementStudio_x64_ENU.exe


1

As long as you change the service account within SQL Server Configuration Manager (SSCM) it will apply all the permissions required (registry, files, etc.). Only thing to be aware of are those rare occasions where SSCM process does not apply all permissions as expected, generally from some WMI issue. I so far have not come across this in new versions of ...


1

You can't completely predict it, but you can backup smaller databases with compression and if the data sets are similar (based on the factors listed below), you can predict a ratio that you may be able to apply to the larger databases. It's easiest to just quote the MSDN on compression here: To calculate the compression ratio of a backup, use the values ...


1

Any time I have seen this error in my environment (0x8009030c and State 14), it has been because the SQL Server engine account was running with a Domain service account, and that account was locked at the time a Kerberos connection was attempted. The connection would work, but it would be kicked down to NTLM. Method 1: Go to register start –> run … ...


1

Before we get into identifying queries that use tempdb, it is very important to understand what all activities in SQL Server (both internal & user activities), which use the tempdb database. Broadly you can classify these into 3 categories:- Kindly read on them. 1.Internal Objects 2.Version Stores 3.User Objects I believe you do not require any tools ...


1

But my question here would be how can i predict or calculate what size of drive would be require to accommodate the backups From Books Online Document. If you see the last part it says that For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. ...


1

Tell me about it? You are speculating. I recommend that instead you investigate. Read How to analyse SQL Server performance. Find the bottlenecks, address them appropriately, based on your findings. Measure before you cut.


1

In order to change the settings, you don't alter the job directly. Go to the primary database in the log shipping setup in SSMS, right click, choose tasks, then ship transaction logs. Click on the button highlighted, which will take you to the following screen:- This screen will allow you to change the UNC path to an IP address



Only top voted, non community-wiki answers of a minimum length are eligible