@swasheck If session 2 has a sufficiently strong hook on the ##table it can survive session 1's disconnection: session 1: create table ## session 2: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; begin transaction; select * from ##;
close session 1 session 2: select * from ## - all OK ..and tempdb.sys.objects still shows the ## as existing.
I do know that
select * from table
gives all columns in the result.
but what happens if we use
select =* from table
Could any one give detailed explanation for this.
I have application running on my server. The problem with this application is that daily I am getting nearly 10-20, System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding only one of my SP. Here is my SP,...
I recently tried to call ALTER TABLE, which got blocked because one of the transactions holds a lock on the related table. With the help of #postgresql IRC channel, I managed to track down the problem to this particular process:d33tah
47329 0.9 0.0 275836 95384 ? Ss Mar03 23:59 po...
Q for the room - has anyone ever heard of using the size of a tlog relative to the data files as some sort of health metric? i.e. "Make sure the tlog is < 3/4 the size of the mdf"
@JNK I've heard something along those lines....but from the same people that like to just go and truncate the log when it reaches some arbitrary size, so
I have two tables:
CREATE TABLE Users(
UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
UserName NVARCHAR(200) NOT NULL,
-- some other columns
CurrentSubscriptionId UNIQUEIDENTIFIER NULL,
)
CREATE TABLE Subscriptions(
SubscriptionId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
Us...
@JNK Nope. I start with my t-logs ~1/3 the size of my data files then let them grow as the workload dictates. I will monitor them for growth, but not for size (to make sure they aren't growing out of control).
I was under the impression there are situations where SQL Server will simplify a query not look at certain underlying tables if they are not useful for the query. i.e. If I LEFT OUTER JOIN to a table but then don't include any columns in the results, or where clause, or any further joins, SQL Server can choose to exclude that table from the query. However, if you've got CASE around each column, I'm guessing it's not able to simplify in this way.
I believe the term is 'Lazy Join' http://stackoverflow.com/questions/20434556/does-sql-server-2012-lazy-join
@MarkSinkinson It won't exclude that left joined table AFAIK because it can still affect the number of records returned even if you don't reference the table in the select or filters
@JNK It shouldn't if the database structure was correct though. You can only have one Current Subscription per user, meaning that the join to a Primary Key should return one row from the Subscriptions table for each record in the Users table.
@swasheck Yes, say you've manually created multi-column stats. Or you created a nonclustered index, and stats were automatically created for the index. Now you might have some duplicate stats alongside the single-column stats that were auto-created on the base table.
@KrisGruttemeyer @JamesLupolt not much ... fewer statistics to update at update time ... fewer stats to consider during planning ... fewer _WA_123809_WTF objects to decipher
@KrisGruttemeyer if you want to see some good impact ... clear the proc cache and compile a query with option (querytraceon 9224, querytraceon 3604) all of the stats considered interesting are loaded.
> However, small inefficiencies can add up quickly when your tables get large, so I recommend that you delete unnecessary auto-created statistics when you create a real index on a column. SQL Server won't do it for you.
There *could* be a situation where the duplicate single-column statistics is chosen instead of the index statistic for a query plan, or vice versa…and the concern is if one is out of date (and therefore not representative of the data in the column) and therefore it generates a less-than-optimal query plan.
To calculate the size footprint of duplicate stats you’d need to dig into some of the internal tables. I haven’t done that, might be worth investigating!
slow storage? wide data? slow network between client and server? subtle variations in the gravitational constant as highlighted by the pioneer probes? — swasheck43 secs ago
@DocHoliday Get a dartboard, place all possible reasons, throw dart. At this point, we'd basically be doing that - we'd be guessing. — bluefeet ♦24 secs ago
@JNK sort of. It looks like we will be using the DB projects (SSDT tools) inside Visual Studio. The main issue with the tsqlt is our environments are basically "reimaged" from production periodically, so installing code inside our DBs doesn't really work very well.
i'm actually coming up against a situation where the plan for a query with a bunch of CTEs is different and the query execution time is consistently faster over against the query without CTEs
I do know that
select * from table
gives all columns in the result.
but what happens if we use
select =* from table
Could any one give detailed explanation for this.
SELECT * FROM tbl WHERE Col1 ='A' AND Col2='B' AND Mindate = MAX(Mindate)
I recently tried to call ALTER TABLE, which got blocked because one of the transactions holds a lock on the related table. With the help of #postgresql IRC channel, I managed to track down the problem to this particular process:d33tah
47329 0.9 0.0 275836 95384 ? Ss Mar03 23:59 po...
I have two tables:
CREATE TABLE Users(
UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
UserName NVARCHAR(200) NOT NULL,
-- some other columns
CurrentSubscriptionId UNIQUEIDENTIFIER NULL,
)
CREATE TABLE Subscriptions(
SubscriptionId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
Us...
What operation: SELECT, INSERT, UPDATE or DELETE has the highest impact on transaction log?
select * from [table_name].
This table has about 500,000 rows and took nearly 16 mins to bring back 500 rows.
What would cause this?
