January 17, 2014 at 4:02 am
Hi Team,
In my database, there is one login/user named HHT, in particular login properties, server roles is marked as 'Pubic'.
in user mappings for particular database db_datareader and public is marked.
if the above user is executing any select statements, is there any change to occur blockings,
if there is chance to occur, how to avoid that blockings....
please suggest
January 17, 2014 at 4:51 am
Any login can have a query that causes blocking. That's just how SQL Server works. Based on what you've described, that's about all I can see. To minimize blocking, go through all the work of ensuring that your server is properly configured, your databases are configured, your queries are well written, your database is properly normalized, your indexes are well-built and your statistics are well-maintained.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2014 at 12:56 am
Thank you for your response.
my query is perfectly tuned, but am a db_datareader user, my query is blocking another queries.
am not using NO Lock.
is there any best way to avoid locking.
January 21, 2014 at 1:05 am
Tune your indexes, tune your queries. Short-lived blocking isn't a problem, long-term blocking is often due to inefficient queries. If you can't tune the queries or doing so doesn't provide enough of a reduction to blocking and it's a read query blocking or being blocked by a write query, consider using one of the row-version based isolation levels.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2014 at 1:17 am
I agree with you, tuning the queries and indexes will help.
but please give one clarity...
is there any possibility that db_datareader can block queries...
January 21, 2014 at 1:18 am
As Grant said
Grant Fritchey (1/17/2014)
Any login can have a query that causes blocking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2014 at 3:26 am
Minnu (1/21/2014)
I agree with you, tuning the queries and indexes will help.but please give one clarity...
is there any possibility that db_datareader can block queries...
Yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply