Tag Info

Hot answers tagged

12

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row... Actually, that depends on the setting of the large value types out of row option, which can be set using sp_tableoption. From the documentation: The default is for MAX values to be stored in-row, up to 8000 bytes, if they fit. Unless you have used ...


7

I have seen this issue before, what seems to happen if you have this type of sequence: create session start session stop session add columnar data (action or field) start session The GUI doesn't show the added columnar data. Your options here would be to directly query the target or to clear/rename the old session definition's XEL files.


6

I have written reports, and actually alerting with it, for clients to monitor multiple servers within their test and production environments. I basically used a SQL Agent Job with a PowerShell step to pull in disk information (win32_volume) into some tables. I then decided to create another PowerShell step that went back and checked if the free space was ...


6

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 ...


5

Remember that S: is relative to the server, not necessarily where you're running SSMS from. I've seen plenty of people query the location of files and find they're not actually accessible from where SSMS is running. If S: is part of a user profile, then it could be associated with the service account even.


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

Not that this would be deemed the answer in your situation, since you already marked it. This is just my take on it, with a bit of testing. I created the event session using SQL Server 2012 instance and SSMS version, minus the application name event. IF EXISTS (SELECT name FROM sys.dm_xe_sessions WHERE Name = 'PySoup tracing') BEGIN DROP EVENT SESSION ...


4

...it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type The PAGEIOLATCH_SH wait occurs when SQL Server is waiting for a data or index page that is not in memory to be fetched from persistent storage. The sys.dm_db_index_physical_stats dynamic management function running in LIMITED mode (the default) needs to read ...


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 ...


3

The disk activity is high because it needs to pull your whole database into RAM to do its analysis. If you call sys.dm_db_index_physical_stats with fewer NULLs, it will be able to run your query on a subsection of the database, which will then run much quicker. Sadly, your TOP 1 isn't stopping it from doing all the calculations, as you're calling the main ...


3

The transaction is being rolled back in the DDL trigger, PreserveTrigger, not the trigger you're trying to drop, trg_write_something. This has nothing to do with the code in the table-level trigger, and nothing to do with permissions either. A sysadmin doesn't get to blindly bypass things like DDL triggers, though it is possible for the code in the trigger ...


3

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? No, what happens is that the execution plan for a stored procedure is cached. Assuming there is enough available memory to continue holding the plan, it won't change unless one of the ...


3

The list you're talking about wouldn't be present in Object Explorer (unless you connected to all of them manually every time you started Management Studio). Your active connections will disappear from Object Explorer when you close down SSMS or reboot, so you can't persist them there (as convenient as that might be). What you might be talking about, ...


3

Am I correct in saying that statistics are only used when creating the execution plan No, out-of-date statistics may cause an optimality-related recompilation of the affected statement. We have an ascending date/time column in one of our largest tables that we query regularly Sub-optimal execution plans caused by predicate values being outside ...


3

Yes, backup & restore is a perfectly reasonable way to copy a database. You will need to use the MOVE option to re-name the files. Here is an example using a backup of a database called DBA and restoring it to DBA_Test RESTORE DATABASE [DBA_Test] FROM DISK = N'DBA_backup_2015_01_11_001025_4030475.bak' WITH FILE = 1, MOVE N'DBA' TO ...


3

This behavior for large objects storage can be controlled by the table setting: exec sp_tableoption N'MyTable', 'large value types out of row', <'ON' or 'OFF'> The reference in the SQL Server 2012 documentation is at: http://msdn.microsoft.com/en-us/library/ms173530.aspx Therefore you can control where the space is used, in-row or stored out of row. ...


3

I found that it is a new feature (don't know what is use of it) Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs ...


3

If you have "lost the data" because of corruption, you may be able to retrieve some of the data from nonclustered indexes. Or if the corruption is only in nonclustered indexes you may have lost nothing and can rebuild the indexes to recover the data. Search for DBCC PAGE and you may find advice from some experts on what to do. If you have "lost the data" ...


3

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain. Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest ...


3

Here are some thoughts on this: Do you know that you should get better compression on the strings that you have tested with? Have you tested those same strings by gzipping outside of .NET? Such as on Linux / CygWin -- DOS port of UNIX utilities / PHP / etc? If you have updated your system with .NET 4.5, then you are using the updated GZipStream. This is ...


2

We cracked this a while back by replacing the RAID controller on the server. The disks and server configuration were fine but it appears that the RAID controller couldn't deal with the IO. We are now in the good place of reads ~ 2ms and Writes at <= 5ms


2

Take a look at the data collector option. Specifically the Disk Usage collection set. The data collector allows you to schedule the collection of various types of information (in your case disk usage) and store them in something called the Management DataWarehouse. Once there it's easy enough to query and/or write reports on the data. This is part of SQL ...


2

This probably ain't going to be right answer, but this works for data you gave us. ;WITH TestData (ColA, ColB, ColC, ColD) AS ( SELECT 1, 1, 'ABC', 'DEF' UNION ALL SELECT 1, 1, 'DEF', 'ABC' UNION ALL SELECT 1, 1, 'GHJ', 'LKJ' UNION ALL SELECT 1, 1, 'LKJ', 'GHJ' UNION ALL SELECT 1, 1, 'ABC', 'HJK' UNION ALL SELECT ...


2

Starting from 2008 onwards Index rebuild is fully logged in full recovery model. If you rebuild huge index in full recovery model its bound to produce too much logs. So in this table you have how many indexes? Are you rebuilding all such indexes in one go. If you are doing this you must consider doing it piece meal. One index at a time Of course you can ...


2

DBCC REINDEX is minimally logged in the SIMPLE or BULK_LOGGED recovery model. If the database is currently in the FULL recovery model, consider toggling it to BULK_LOGGED for the index maintenance. See http://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


2

Listen to your adviser. By restoring a backup, you are essentially replacing the database schema and data. You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY. Once your backup is in place, put the DB back into HA and ...


2

Method 1 : YOu can use the below VB code in SQL agent job to find the information on packages running on sql server 2012: '... ' Declare and instantiate objects here. Dim app As New Application '... ' Create a RunningPackages collection, named pkgs, and fill it ' with the running packages from the application object. Dim pkgs As ...


2

If you are connecting from outside the domain you can either 1) Use SQL authentication to login. If you have enabled mixed mode during the install you will have an sa account and you can create a sql login for yourself 2) There are other tricks, such as enabling named pipes and then authenticating against the IPC$ share with your domain account and then ...


2

SSMS isn't as full-featured as some other editors out there when it comes to visual formatting. I think the closest you'll get is using word wrap from Tools -> Options and then manually sizing your SSMS window to force it to a smaller column width. The bottom row of SSMS will tell you what column number your cursor is on, which may be helpful to size ...


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 ...



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