![]() ![]() I’m going to take this to another forum as well but I thought people here might find it interesting.Microsoft SQL Server is the Relational database model which describes the inter-related relation between the data and it application by means of the storage medium which arranges and set the data in the well-defined manner which provides the easy access to the user and this server is created by the Microsoft for the storage and well arrangement of the required data using the query language that will includes the T- SQL and ANSI SQL and basically used for the well integration of the logical and inter-related files which describes the relation between them and provide them full access when required such that each logical or inter-related entry must take place only once in the database of the SQL server using the concept of the primary key which will uniquely identify the data items and avoid the problem of the redundancy of the data. I suspect it doesn’t since I was still seeing CXPACKET waits. And the final complication is that the stored procedure is using a view so I don’t know if setting the hint in the SP actually forces the view to run without parallelism. The question is this: am I fixing it because of MAXDOP = 1 or because I’ve reset the plan cache? One of these stored procedures had the "OPTION (MAXDOP 1)" hint but still blocked itself until I set the server Max DOP. I’ve found things work if I set the server to a Max DOP of 1 instead of the default 0 and re-run the stored proc. Lots and lots of CXPACKET waiting processes all showing taskid the same as the blocking taskid. The problem is I occasionally get processes blocking themselves. I’ve managed to decrease run time by 75%-80%. I have made some "improvements" to the system by moving some of the work out of SSIS and into SQL stored procedures. I’ve inherited a system that has a nightly ETL process. Thanks to all those who replied to the original survey!Īctually, speaking of plan cache. Be careful when doing this on a production server… One thing to be aware of: if you change the MAXDOP setting for the server, it will flush the plan cache when you do. In other words, what should your MAXDOP be set to? It depends! :-) ![]() The key point when making any change for performance tuning is to test the change before putting it into production and work out which setting works best for your workload on your system – rather than blindly following 'this is the best way' rules for settings that people publish. This even works if the legacy code uses MAXDOP query hints, because the resource governor workload group MAXDOP setting *cannot* be overridden. Using the resource governor as I described above can be a very easy way to mess around with the MAXDOP setting – especially for applications with legacy code that you cannot change, and you don't want to set a server-wide MAXDOP setting. Consider using the cost threshold for parallelism setting – see Jonathan's recent post here. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |