Tag Info

Hot answers tagged

10

SampleTable is contained on 4714 pages, taking about 36MB. Case 1 scans them all which is why we get 4714 reads. Further, it must perform 1 million hashes, which are CPU intensive, and which ultimately drives the time up proportionally. It is all this hashing which seems to drive the time up in case 1. There is a start-up cost to a hash join (building ...


8

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...


7

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are: YYYYMMDD YYYY-MM-DDThh:nn:ss[.mmmmmmm] ----------^ yes, that T is important! Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:* SET LANGUAGE FRENCH; SELECT ...


6

There are couple of ways to do such forensic analysis : Using Default trace (provided that the default trace is running). The default trace create 5 *.TRC file that are recycled when they arrive to 20 MB size. : -- create a test procedure .. so that we can drop it use test_kin go create procedure usp_ABOUT_TO_DELETE as select name from sysobjects where ...


6

@Kin is right about getting this info from the default trace, if it is in fact still there (depends on how busy your server is, and how long ago this happened). To get an e-mail immediately, though, instead of having to remember to check the default trace all the time just in case something happened, you can do this from a DDL trigger. Database Mail config ...


6

If you have default trace running and hopefully it is not recycled, it will be logged in there : SELECT SPID ,LoginName ,NTUserName ,NTDomainName ,HostName ,ApplicationName ,StartTime ,ServerName ,DatabaseName ,CASE EventClass WHEN 46 THEN 'CREATE' WHEN 47 THEN 'DROP' ELSE 'OTHER' END AS EventClass ,CASE ObjectType WHEN ...


5

Not knowing your data, the view definition would be: SELECT [SalesID], [Delivery Date], ROW_NUMBER() OVER (PARTITION BY [SalesID] ORDER BY [Delivery Date]) AS DateNo FROM [WhatEverTable] http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx ROW_NUMBER() by itself will return a running count of the rows, but when you add the ...


5

If your log file is corrupt, my concern is that a backup/restore would retain the corruption. My approach (which would probably be faster to complete) would be: Run a full backup. Detach database. Delete/rename the log file. Attach the database and rebuild the log file. Take a second full backup. To attach the database and rebuild the log file, it's ...


4

Try the following: select office_id,[1] as value_1, [2] as value_2, [3] as value_3, [4] as value_4 from ( select office_id, setting_value, row_number() over(partition by office_id order by office_id) rnk from office_settings ) d pivot ( max(setting_value) for rnk in ([1], [2], [3], [4]) ) piv; Or without using PIVOT (generates the same ...


3

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'. That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E: SELECT ...


2

This is now a known bug (present only in SQL 2012), that Microsoft is currently addressing. Workaround is to give user ALTER TRACE permission until is resolved. You can track status on Connect.


2

In think your problem is that gourmet_id is declared as CHARACTER(30). If you change it to CHARACTER VARYING(30), it should work much faster. This is what happens: -> Seq Scan on counters c (cost=0.00..250342.85 rows=2725633 width=16) (actual time=0.009..1610.743 rows=2751732 loops=1) Filter: ((counter_name)::text = 'FnfHit'::text) Rows Removed by ...


2

If you look at the two execution plans you will see that the estimated cardinality is different in each case. When the actual value is in the SELECT statement the optimiser can use the cardinality of that one, actual value to choose an access path. When the SELECT has a variable the optimiser cannot know whether you have provided a very popular or very ...


2

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins ...


2

INSERT dbo.mytest(first_name, table_id) SELECT fn, ti FROM ( VALUES('f1', @id_for_test), ('f2', @id_for_test), ('f3', @id_for_test), ('f4', @id_for_test), ('f5', @id_for_test), ('f6', @id_for_test) ) AS x(fn, ti) WHERE NOT EXISTS ( SELECT 1 FROM dbo.mytest WHERE first_name = x.fn AND table_id = ...


2

Microsoft has a KB article about this behavior. You got a 32-bit OS with 32-bit application, so the process' address space is 4 GiB. AWE can be used to access more memory, but it is limited only for buffer pool usage. As per the documentation: The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be ...


2

As a matter of logical transactions @AlwaysLoadingData has you covered. But, have you tuned your log-files? Is your log file contiguous. Did you pick a size and per-allocate the file(s) Have you created an appropriate number of log-files do you have an excessive number of virtual log files. Run DBCC LOGINFO and make sure you don't have >1K rows (I think) ...


1

Transaction size is unlikely to be the primary cause of import slowness. There are legitimate reasons to break up transactions, even within a single table or to import a whole set of tables in a single transaction as you do. Also, for most requirements, large or small depends on the number of rows, not the number of tables. Reasons for larger ...


1

Sounds like a bad plan to me - and possibly around statistics. When you insert the data into a temp table, you can get statistics auto created on it. If your statistics think there aren't many records for that combination of stock and year, it could choose a really bad plan. Try updating your statistics and seeing how it goes. Or put a better indexing ...


1

There are a lot of ways to do this one that go beyond what I listed, but here's a start. 1) You could try the SQL Server Migration Assistant for Oracle. It's free and usually works well for me for smaller or medium sized datasets. http://www.microsoft.com/en-us/download/details.aspx?id=28766 2) Create a SSIS package. 3) You could create a linked server ...


1

Both missing indexes and long-running queries are available in DMVs, with the caveat that DMVs can get refreshed on some events, such as service restart, certain sp_configure changes, etc. Here's a missing index query from Bart Duncan's blog post, Are you using SQL's Missing Index DMVs?: SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) ...


1

How do I avoid sort in the explain result? Actuall I didn't ask any sort in the SQL Statement. You've asked for rows to be aggregated. One way to do this is to sort the data set and then scan it to collapse out duplicates. This can be faster than hash aggregation, which is the other way PostgreSQL knows how to do grouping. So while you didn't ...


1

If you have the Enterprise version of SQL Server, then I would recommend doing this using Change Data Capture (CDC). This is much easier and cleaner than setting up triggers to watch for changed data. Plus it reads the transaction log for changes and then logs those changes in system tables. You can use SSIS for your ETL. If you have 2012 installed, there ...



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